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.

 

[thiggins]

Error

It's not a problem, but I have to go back to the AS400 program that outputs the files used in this to make sure I have everything setup correctly. The names are referenced in the CL on the AS400 so I believe I need to start there if I'm going to change.

 

[thiggins]

Subscript Out of Range

Ok - I changed the variable names. See Below:
'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\opnV1.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$ = "OPNV1"
'ReportCSV$ has the name of the worksheet with .CSV
ReportCSV$ = "OPNOV1.CSV"

When I run at the statement
Workbooks.open filename = Report$ (the OPNV1.csv is open on my PC)

Then if I continue through the code
ActiveWorkbook.SaveAs Filename:=Reportwb$, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
(The OPNORDS2.XLS is open - and the OPNV1.CSV is not)

Then the next line of code is this and it returns the Runtime Error 9:
Worksheets(ReportSN$).Rows("1:1").Font.Bold = True

 

[Norie]

Which workbook is the worksheet called OPNV1 in?

Which workbook is the open event in?

 

[thiggins]

Error

The workbook that is open is the opnords2.xls and the worksheet OPNV1 is in this workbook. The open event is in the RunQry1.xls sheet 1.

 

[Norie]

Does this work?

Workbooks("opnords2.xls").Worksheets(ReportSN$).Rows("1:1").Font.Bold = True

 

[thiggins]

Perfect!

Thank you so much. That was the trick. I had to add the Workbooks("opnords2.xls"). portion to a couple other lines of code to get the complete project to work.
Again I can't express my full appreciation for the time you took out of your day to help me with this. I think I have a clearer understanding now for future projects.

 

[Norie]

No problem.

The reason it wasn't working was because you needed to explicitly reference which workbook the worksheet was in.

Excel VBA can be a bit difficult when you don't do that.