Code Runs using Command Button - Errors using On Open

[thiggins]

I have a Excel 2003 running on XP that excutes perfectly when the procdure is executed by clicking on a command button. I wanted the procedure to run when the Excel file opens so replaced the On Click with Workbook_Open. However now I encounter an error 9 subscript out of range. I'm at a loss because the only thing I've changed is how the procedure is executed.

 

[Norie]

Can we see the code?

 

[thiggins]

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

 

[Norie]

Where is the error occurring?

 

[thiggins]

Error Occurs

The error occurs at this line:
Workbooks.Open Filename:=ReportSN$
'*Worksheets(ReportSN$).Rows("1:1").Font.Bold = True

Note that on the sample code I attached that is the version that runs fine as long as I execute it clicking the command button. I took the code copied it and replaced the top line with:
Private Sub Workbook_open()

It is when I run this sub routine that I receive the error above. In the immediate window ReportSN$ shows the file so in my mind it must be finding it?
I'm really new at this so I appoligize if I'm not clearly stating the problem.

 

[Norie]

Is ReportSN$ the name of a worksheet or workbook?

You seem to be using it first to open a workbook, then to refer to a worksheet.

By the way since you haven't told Excel which workbook the worksheet is in it will look in the active workbook.

That may or may not be what you want.

Does this work?

Set wb=Workbooks.Open(Filename:=ReportSN$)
wb.Worksheets(ReportSN$).Rows("1:1").Font.Bold = True

Or this

Workbooks.Open Filename:=ReportSN$ 
ThisWorkbook.Worksheets(ReportSN$).Rows("1:1").Font.Bold = True

The reason for the 2 different codes is because I don't actually know which workbook the worksheet is in.

 

[thiggins]

Error

No neither one of these code snippets worked. They both presented the same error. Note however when the errors occurs the opnords.xls is the active window on my pc screen. It presents a runtime error '1004' stating it can't find the opnords.xls, which to me seems rather strange because the file is open on my screen? Then when I select debug is when it points to the line of code I sent earlier.

 

[Norie]

I'm getting confused.

There is no opnords.xls in the code, though there are opnords2.xls and opnords2.csv.

I'm also still unsure about the worksheet/workbook thing.

In your comments here you say ReportWB$ is the XLS file.

'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

You then try and open a file using ReportSN$, which in the above you say is the name of the workbook.

Workbooks.Open Filename:=ReportSN$

Perhaps you could rename your variables to make it easier to distinguish between them.

 

[thiggins]

Typo

My mistake - the error does state opnords2.xls. Which is displaying on the screen in the unformatted version when the error occurs. I will try your suggestion of changing the variable names but that will take sometime as I have to modify the AS400 program.
I guess my biggest confusion is how can such as simple thing as changing it from executing via a click versus on open can cause this problem. Trying to understand for future reference.

 

[Norie]

Like I said it's to do with referencing workbooks/worksheets.

Why is it a problem changing the variable names?