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

My guess is there is a blank row at row 272 in your spreadsheet.
--
Dave Hargis, Microsoft Access MVP


"Rod Manson" wrote:

> 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

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!

"Klatuu" wrote:

> My guess is there is a blank row at row 272 in your spreadsheet.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Rod Manson" wrote:
>
> > 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