Sample Code
I appoligize for pasting the code this way but tried the other methods and could not get them to work.
Private Sub RunQry1_Click()
'Done$ has the path and file name of the "all done" file
Done$ = "W:\RPG_EXCEL\Done1.txt"
'Report$ has the path and name of the CSV file in the IFS.
Report$ = "W:\RPG_EXCEL\opnords2.CSV"
'ReportWB$ has the path and name of the XLS file in the IFS.
ReportWB$ = "W:\rpg_excel\opnords2.xls"
'ReportSN$ has the name of thw worksheet.
ReportSN$ = "OPNORDS2"
'ReportCSV$ has the name of the worksheet with .CSV
ReportCSV$ = "OPNORDS2.CSV"
'Parms1$ has the parameters that will be passed with the RMTCMD.
'Parms1$ = "PARM(" & QueryNam1 & " " & QueryLib1 & ")"
'Shell$ has the Remote Command string to call the program
'on the iSieries that runs the query.
Shell1$ = "RMTCMD //QS102KK1M CALL RPG_EXCEL/RUNQRY1"
'------------------------------------------------------------------------
'
'Does Done.txt already exist from last time?
FindIt = Dir(Done$)
'If the file Done.txt has been found, delete it.
If Not Len(FindIt) = 0 Then
Kill Done$
End If
'--------------------------------------------------------
'Does the Excel workbook exit from last run?
FindIt = Dir(ReportWB$)
'If the file Done!>txt has been found, delete it.
If Not Len(FindIt) = 0 Then
Kill ReportWB$
End If
'------------------------------------------------------------------------
'Call the program on the iSeries
Shell (Shell1$)
'------------------------------------------------------------------------
'The following while - Wend loop will keep Excel looping until
'the RMTCMD command has completed. This will occur while the
'length of the FindIt variable is equal to 0. Excel will remain
'busy until it finds the file Done.txt, thus making the length
'of FindIt >0 and ending the loop.
'Check to see if Done.txt can be found yet
FindIt = Dir(Done$)
'Check to see if the length of the FindIt variable is equal to 0
While Len(FindIt) = 0
'Continue to check if Done.txt is there yet.
FindIt = Dir(Done$)
Wend
'------------------------------------------------------------------------
'Here when out of the looking for Done.txt loop.
'Done.txt has been found
'Open the CSV file.
Workbooks.Open Filename:=Report$
'------------------------------------------------------------------------
'Activate the CSV file
'Windows(SheetCSV$).Activate
'Save it as an Excel Workbook.
ActiveWorkbook.SaveAs Filename:=ReportWB$, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
'Make the Headings' fonts bold.
Worksheets(ReportSN$).Rows("1:1").Font.Bold = True
'Set the columns to the right width.
Worksheets(ReportSN$).Columns("A:IV").AutoFit
'Lock the titles so the headings won't scroll off the screen
Worksheets(ReportSN$).Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub