Import Multiple Excel Sheets into Access

[DThreadgill]

I have a spreadsheet with about 35 tabs that I need to import into one table in Access. I found this link http://www.thescripts.com/forum/thre...iple+tabs.html and changed some things within the code.

First off - I've written a piece of code that changes the name of each sheet (Sheet1, Sheet2, etc), deletes the blank rows and formats a couple of the columns to text and date format. It then saves and quits the application as well as sets it to nothing.

When I try to run the code that is within the above link I get
*RunTime Error '91' Object variable or With block variable not set*

Here's the code:

Dim excelapp As New Excel.Application
Dim excelbook As New Excel.Workbook
Dim excelsheet As New Excel.Worksheet
Dim intNoOfSheets As Integer, intCounter As Integer
Dim strFilePath As String, strLastDataColumn As String
Dim strLastDataRow As String, strLastDataCell As String
 
strFilePath = "\\drivemapping\spreadsheet.xls"
 
Set excelbook = excelapp.Workbooks.Open(strFilePath)
 
intNoOfSheets = excelbook.Worksheets.Count  <--It finds 35 sheets
 
For intCounter = 3 To intNoOfSheets 'start at sheet 3
  excelbook.Worksheets(intCounter).Activate
    'The next 3 lines will obtain the last data cell reference for each Worksheet
    strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64) <--RUNTIME ERROR '91' HERE
    strLastDataRow = Selection.SpecialCells(xlLastCell).Row
        If strLastDataRow = 1 Then   'sheet has no rows to import
        GoTo Line1
        End If
    strLastDataCell = strLastDataColumn & strLastDataRow    
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel11, "tmp", strFilePath, False, _
                 excelbook.Worksheets(intCounter).Name & "!A2:" & strLastDataCell  'A1 is header row so start on A2
Line1:
Next
 
excelbook.Close
excelapp.Quit
Set excelapp = Nothing

However...it doesn't do it ALL the time. I just got the error...ended it and then re-ran and it worked fine. I'm using Access 2003 on WinXP SP2. Just want it to be as stable as it possibly can be before I turn it over to the user.

I looked up the error message here & on google but none of the explanations really applied to my scenario. Any help is much appreciated.

[FishVal]

Hi, there.

Try to refer Selection object through Excel.Applicati on object.
e.g.
instead
Selection.blabl abla
excelapp.Select ion.blablabla