Excel failed on second trip - Error: 1004

[Jim Pan]

I create a routine that will read an excel file and send the content to a
text file. If there's only one excel file, the routine works. If there're
multiple, then the second call to that sub will failed with 1004 -
Application-defined or object-defined error
here is partial code below. PLEASE HELP.

Dim objExcel As Excel.Application
......

Set objExcel = New Excel.Application
objExcel.Workbooks.Open strFileName --> passed from calling routine
For R = 2 To Range("A65536").End(xlUp).Row --> FAIL HERE
.........
next R

objExcel.Quit
Set objExcel = Nothing


The second time, the error happend on "For" statement, the open statement
does not failed. I add "objExcel.Worksheets("Sheet1").Activate" as test, it
failed.

There's no EXCEL object from task manager.

 

[Peter T]

If you are automating Excel you need to qualify everything back to the
application object

Set objWb = objExcel.Workbooks.Open(strFileName)
For R = 2 To objWb.Worksheets(1).Range("A65536").etc

or you might be able to get away with
objExcel.ActiveSheet.Range(etc
(if sure the correct wb is active)

Regards,
Peter T

 

[Jim Pan]

I have qualify the worksheet also, after that, everything worked. Thanks
for pointed out simple mistake. I was trying to have minimum code as my vb
module grew large.

Thanks!