By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,078 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,078 IT Pros & Developers. It's quick & easy.

Import Multiple Excel Sheets into Access

P: 57
I have a spreadsheet with about 35 tabs that I need to import into one table in Access. I found this link 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:
Expand|Select|Wrap|Line Numbers
  1. Dim excelapp As New Excel.Application
  2. Dim excelbook As New Excel.Workbook
  3. Dim excelsheet As New Excel.Worksheet
  4. Dim intNoOfSheets As Integer, intCounter As Integer
  5. Dim strFilePath As String, strLastDataColumn As String
  6. Dim strLastDataRow As String, strLastDataCell As String
  8. strFilePath = "\\drivemapping\spreadsheet.xls"
  10. Set excelbook = excelapp.Workbooks.Open(strFilePath)
  12. intNoOfSheets = excelbook.Worksheets.Count  <--It finds 35 sheets
  14. For intCounter = 3 To intNoOfSheets 'start at sheet 3
  15.   excelbook.Worksheets(intCounter).Activate
  16.     'The next 3 lines will obtain the last data cell reference for each Worksheet
  17.     strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64) <--RUNTIME ERROR '91' HERE
  18.     strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  19.         If strLastDataRow = 1 Then   'sheet has no rows to import
  20.         GoTo Line1
  21.         End If
  22.     strLastDataCell = strLastDataColumn & strLastDataRow    
  23.       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel11, "tmp", strFilePath, False, _
  24.                  excelbook.Worksheets(intCounter).Name & "!A2:" & strLastDataCell  'A1 is header row so start on A2
  25. Line1:
  26. Next
  28. excelbook.Close
  29. excelapp.Quit
  30. Set excelapp = Nothing 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.
Oct 29 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,653
Hi, there.

Try to refer Selection object through Excel.Application object.
Oct 29 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.