Question

Locked

how do I open and update EXCEL sheetsheet from AS400?

By ChrisCPbucc ·
the batch file (mybatfile.bat) is called by a RPG program from AS400, i5/OS
the first four lines executes properly, then it goes into a TIMW that never ends.
If I exclude the c:\spreadsheet\opexcels.exe statement (my primary need), it executes properly.


*************************************************************************************
*mybatfile.bat

cd\
c:
cd c:\SPREADSHEET

rem *****the line below creates an excel spreadsheet ESA002F2.xls
"c:\program files\IBM\Client Access\rxferpcb" ESA002F2.dtf mylogon mypassword

c:\spreadsheet\opexcels.exe
copy c:\spreadsheet\ESA002Fz.xls c:\spreadsheet\ESA002F2.xls
del c:\spreadsheet\ESA002Fz.xls


************************************************************************************
*OPEXCELS.exe
*opexcels.exe is a vbscript with the following lines
*it runs perfectly as a standalone

Private Sub Form_Load()
'******VBScript to open excel spreadsheet, update cell, save then close
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\spreadsheet\es00pre2.xls")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True 'True
objExcel.Application.Quit
End
End Sub
Private Sub cmdClose_Click()
Unload Me
End
End Sub


*************************************************************************************
*spreedsheet es00pre2.xls has the following automatic VBscript

Private Sub Workbook_Open()
'
'
' ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
ChDir "C:\spreadsheet"
Workbooks.Open Filename:="C:\spreadsheet\esa002f2.xls"
Range("A2").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "=IF(R[-1]C=""E2AID"",""ALTERNATE ID"","""")"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C=""E2SSN"",""MEMBER SSN"","""")"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C=""E2AnFD"",""ANNUITY FUND"","""")"
Range("D2").Select
Range("A3").Select
Selection.EntireRow.Insert
Range("A2:AZ2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:AZ2").Select
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Columns.AutoFit
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\spreadsheet\ESA002Fz.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
'Application.DisplayAlerts = True
Application.Quit
End Sub

Private Sub Wookbook_BeforeClose(Cancel As Boolean)
'close workbook without saving changes
Me.Save = True
End Sub

*****************************************************************************************************
*To test opeining EXCEL from AS400, I tried running the RUNRMTCMD command from the As400 command line

RUNRMTCMD CMD('c:\spreadsheet\mybatfile.bat') RMTLOCNAME('xxx.xx.xx.xx' *IP) RMTUSER(mylogon) RMTPWD('mypassword') WAITTIME(120)

it returns a "Command did not complete successfully" message

Help. I am not a seasoned AS400 user.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums