how do I open and update EXCEL sheetsheet from AS400?

5 pts.
Tags:
AS/400
Excel Scripts
this batch file 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:spreadsheetopexcels.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 filesIBMClient Accessrxferpcb" ESA002F2.dtf mylogon mypassword

c:spreadsheetopexcels.exe copy c:spreadsheetESA002Fz.xls c:spreadsheetESA002F2.xls del c:spreadsheetESA002Fz.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:spreadsheetes00pre2.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:spreadsheetesa002f2.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:spreadsheetESA002Fz.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:spreadsheetmybatfile.bat') RMTLOCNAME('xxx.xx.xx.xx' *IP) RMTUSER(mylogon) RMTPWD('mypassword') WAITTIME(120)

it returns a "Command did not complete successfully" message

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    wait, the spreadsheet is on your PC and you want to open it there IN EXCEL, and then you want the 400 to update it? you want to have the spreadsheet on a server where it can be mapped to the 400's IFS, then use the HSSF (or similar) API's to work with the spreadsheet file directly.
    8,200 pointsBadges:
    report
  • TomLiotta
    I tried running the RUNRMTCMD command from the As400 command line... How is the remote command service defined on your PC? Is it started? it returns a "Command did not complete successfully" message The joblog should have at least one related diagnostic message before the "Command did not complete successfully" message. What other messages show up? Tom
    125,585 pointsBadges:
    report
  • chalenger
    Would you check the public Aut. for Runrmtcmd !!
    480 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following