Excel file - not all records being imported

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel file that I receive from a vendor every month in a .zip file.
I extract it and then attempt to import it into my Access 97 mdb. However,
I don't get all of the records. The file is usually between 25000 to 27000
records. I have tried using a macro and putting ! at the end of my sheet
name. No matter what I do, I am only able to import 16383 records of the
file. Is there a governor somewhere in Access that is restricting the number
of records I can import?
 
If you're not using TransferSpreadsheet action or method to import the EXCEL
file's data, then the EXCEL file will be truncated at 16384 records because
the method uses EXCEL 95 format (which has a worksheet limit of 16384 rows).
I am guessing that the first row contains headings, hence you get only 16383
rows of data.

Don't use File | Get External Data ... | Import method because that is one
the "truncating" methods. Use macro or VBA code to use the
TransferSpreadsheet method; see Help file for more details.
TransferSpreadsheet allows you to specify a more current version of EXCEL
for the EXCEL file's format.
 
Thanks for your suggestion. I have been using the Transferspreadsheet
method in a VBA module. I call this from a form and supply all of the
necessary info. I tried it again using an old file that had 23096 recprds
inlcuding the column headers and I got 13999 records. Is there a limit of
characters that can be imported using Transferpsreadsheet? This file is very
detailed and the fields are in A:AC?

Trentongg
 
When I responded a few minutes ago to your post, I forgot to insert my
function that I'm using, here it is.

Private Sub Import_HFRX_CLAIMS_File_Click()
Dim frmformname As Form
Set frmformname = Forms!frm_delete_import1
Dim strPath_List As String
Dim strYear_Month_Subfolder As String
Dim strFile_Name As String
Dim strTable_Name As String
Dim dbs As Database

Set dbs = CurrentDb
strPath_List = frmformname.Path_List & "\"
strYear_Month_Subfolder = frmformname.Year_Month_Subfolder & "\"
strFile_Name = frmformname.File_Name
strTable_Name = frmformname.Table_Name

DoCmd.TransferSpreadsheet acImport, 8, strTable_Name, strPath_List &
strYear_Month_Subfolder & strFile_Name, True

end sub
 
Your code looks fine and should work correctly.

Do the records that import show correct data in the fields? In other words,
do you see any data that appear to be "out of place", as if some cells in
the EXCEL file were skipped during the import? Do any of the EXCEL cells
being imported contain Null values? Are there any blank rows in the
spreadsheet's data?

--

Ken Snell
<MS ACCESS MVP>
 
Back
Top