I am attempting to import an Excel file into Access. I am getting a 3125 error. It shows the strLastDataColumn = to @ or some other symbol, when it should be a letter, ie 'J' or 'DA'. What am I doing wrong? ANy solution?
For intCounter = 1 To intNoOfSheets
imported = "Y"
excelbook.Worksheets(intCounter).Activate
'The next 3 lines will obtain the last data cell reference for each Worksheet
strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column)
strLastDataRow = Selection.SpecialCells(xlLastCell).Row
strLastDataCell = strLastDataColumn & strLastDataRow 'e.g. J123
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, True, _
excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
Next
For intCounter = 1 To intNoOfSheets
imported = "Y"
excelbook.Worksheets(intCounter).Activate
'The next 3 lines will obtain the last data cell reference for each Worksheet
strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column)
strLastDataRow = Selection.SpecialCells(xlLastCell).Row
strLastDataCell = strLastDataColumn & strLastDataRow 'e.g. J123
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", strFilePath, True, _
excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
Next