Import Excel sheets to Access

[Rod Manson]

I have used the following code to import excel data into Access database
tables. It works but won't bring in any more than the first 270 records. Can
anyone explain why please and let me know how to resolve the problem?

Thanks.

Public Sub TestCode1()

Dim strPrompt As String, strTitle As String, strDefaultExcelSheet As String,
strExcelSheetName As String, strTable As String

DoCmd.SetWarnings False

strPrompt = "Enter the full file Path and name of the
Spreadsheet you want to import.."
strTitle = "Spreadsheet Location and Name"
strDefaultExcelSheet = "J:\CADPHH\DC3160\Modelling\Traffic
Data\O&D CLEANED data\Survey1.xls"
strExcelSheetName = InputBox(strPrompt, strTitle,
strDefaultExcelSheet)
strTable = "ImportTest"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
strTable, strExcelSheetName, True

DoCmd.SetWarnings True
DoCmd.OpenTable strTable

End Sub

 

[Klatuu]

My guess is there is a blank row at row 272 in your spreadsheet.

 

[Rod Manson]

Thanks Dave. I've sorted it. There were multiple worksheets and it was not
loading the correct one as I missed the 'range' variable from the end of the
docmd command!