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
- 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
I looked up the error message here & on google but none of the explanations really applied to my scenario. Any help is much appreciated.