Importing Excel data

[knarlyd@hotmail.com]

I have an Excel spreadsheet that doesn't have any type of index, so I add one after the import. I don't understand why Import routine #1 doesn't bring in *all* the data, only some of it. I looked over the spreadsheet dozens of times but can't see why (I'd include it but it's confidential data.)
If however, issue method #2, all the data is imported, however it deletes the table, then performs the import.
I prefer #1 as it doesn't delete the table contents which include the index.

Here are the two import routines (hopefully someone can tell me why #1 doesn't pull same results?)

#1:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblprivate", "c:\spreadsheet.xls", True

#2
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Import", _
filename:=strPathFile, _
HasFieldNames:=True, _
Range:=strRange
db.TableDefs.Refresh
db.Close: Set db = Nothing

 

[Cronk]

Alternatively, you could create a link to the spreadsheet in Access, then use an append query to add the Excel data to your table.

 

[knarlyd@hotmail.com]

Alternatively, you could create a link to the spreadsheet in Access, then use an append query to add the Excel data to your table.

I will c what I can do with that. Thanks!

 

[cstickman]

knarlyd - here is my code for importing an excel spreadsheet.

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tempolddefectlog", selectFile, True, "Defect Log!C7:T200"

What I do is import a range of cells into a temp database in this case it is called tempolddefectlog and from then I run a delete query to remove blank rows. Then it is transferred into my database table. I picked a big range because every day it could fill 10 rows or 200 rows.

The Selectfile in the code you can omit as I have the user pick the Excel File instead of using the same one over and over. I can supply you with that function if needed.

 

[knarlyd@hotmail.com]

knarlyd - here is my code for importing an excel spreadsheet.

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tempolddefectlog", selectFile, True, "Defect Log!C7:T200"

What I do is import a range of cells into a temp database in this case it is called tempolddefectlog and from then I run a delete query to remove blank rows. Then it is transferred into my database table. I picked a big range because every day it could fill 10 rows or 200 rows.

The Selectfile in the code you can omit as I have the user pick the Excel File instead of using the same one over and over. I can supply you with that function if needed.

I think I'll look into this one too!
Thanks!