Error when opening Excel sheet only from Access VBA program

[Gary Thompson]

I have written a VBA program within Access 2003 to gather data from user
maintained spreadsheets. I am using the following code within a loop to
iterate through spreadsheets within a folder, open the sheet and extract
information into an Access database. Recently some of the spreadsheets have
returned an internal subscript out of range error, but only when opened via
the VBA program, NOT when opened from Windows Explorer. As you can see I am
disabling events when I open the sheets. Any suggestions?

Set apExcel = New Excel.Application
apExcel.EnableEvents = False
Set wkbk = Workbooks.Open(filename:=strFilename, UpdateLinks:=False,
ReadOnly:=True, _
Notify:=False)
wkbk.Worksheets("Base Data").Select

 

[Klatuu]

Are you sure all workbooks have a sheet named Base Data?

 

[Gary Thompson]

Good question, made me go back to check the spelling etc.. Answer is yes.
All of the spreadsheets are built from a common template. Users are allowed
to add tabs, but not to rename or delete the base tabs. In this case they
added tabs, but did not mess with the tab name I was looking for.

 

[Klatuu]

I did some testing and the only way I could recreate the error was to select
a sheet name that doesn't exist.

Might there be variations in the name like Base_Data? The _ may not always
be visible in sheet name in Excel.

What I might suggest to avoid the error and determine if there is a problem
in the name would be to loop through the worksheets selection looking for the
name. Also, I notice you are not using complete referencing. When using
Excel in automation from Access, this is very important. I have seen cases
where Access will get confused over which instance of Excel the object
belongs to and actually create it's own instance. Then when you quit Excel,
an instance of it will still be running.

Dim lngSht As Long
Dim blnFoundSheet As Boolean

Set apExcel = New Excel.Application
apExcel.EnableEvents = False
Set wkbk = apExcel.Workbooks.Open(filename:=strFilename,
UpdateLinks:=False,
ReadOnly:=True, _
Notify:=False)
With apExcel.wkbk
For lngSht = 1 to .WorkSheets.Count
If .Worksheets(lngSht).Name = "Base Datat" Then
blnFoundSheet = True
Exit For
End If
Next lngSht
End With

If blnFoundSheet Then
wkbk.Worksheets("Base Data").Select
Else
"No Base Data Sheet Found in " & strFileName
End If

 

[Gary Thompson]

Interesting. This would explain why I occasionally see phantom instances of
Excel hanging around when the program ends. I will update the code and see
if that helps.

 

[Gary Thompson]

I am still walking through the code with the changes you suggested. The
problem is that the error is not occurring in Access. It is occuring in the
spreadsheet upon the apExcel.Workbooks.Open statement. If the error were
within the Access code it would make more sense to me. I don't even get to
the worsheet selection statement.