Importing Excel, more data type problems

[Guest]

The Excel file I have to import is generated automatically by QuickBooks so I
have no control over the format, etc., it comes out in. That said, I have
(at least) two problems importing this data.

1) I need to use the first row of Excel data as column names in my imported
table -- except not all the fields in the first row have data in them. Is
this a potential problem?

To create the necessary table to import the Excel data into, I created a
table ("Imported_RawData") using:

Dim col As ADOX.Column
col.Name = "Field1"
col.Type = adVarWChar
col.DefinedSize = 100
col.Attributes = 0
Set col.ParentCatalog = cat
tbl.Columns.Append col

etc...

I use "Field1", etc., as column names for all the empty fields in the 1st
row of the Excel file, and the text names otherwise. When I run the code for
creating the table, all looks fine. However...

2) After the 1st row of data in the Excel file, each subsequent field of
each column is itself either blank or text or number or date, etc. And when
I run the following code, I get a Run-time error '13' type mismatch:

Dim stDocName As String
stDocName = "C:\Data\RawData.xls"
DoCmd.TransferSpreadsheet acImport, "Imported_RawData", stDocName, True,
"Sheet1$"


The frustrating part of this is that when I manually import the table with
the wizard, the data comes into Access perfectly! But using this manual
import is not an option for the application, unfortunately. Is there a way
to automate the wizard process in VBA or any other (non-manual) way to get
this data into Access?

Thank you very much for any suggestions!

 

[Ken Snell \(MVP\)]

If you want to use the first row in the EXCEL file as your field names, then
every cell in that first row for each of the "field names" must have a name
in it; and that name must be the same as the field name in the table into
which you're importing the data, and they must be in the same order as the
fields in the table. If you set the "HasFieldNames" as False in the
TransferSpreadsheet action, it will treat the first EXCEL row as data, and
those first row values do not need to match the table's field names (but
they still must be in the same order).

If your table has an autonumber field in it, and if that field is not the
very last one in the table (in design view, it's the one at the bottom),
then the TransferSpreadsheet will try to import data into that field from
the spreadsheet from the column corresponding in order to that autonumber
field. Move that field to the "end" of the field list for the table if it's
there.

 

[Guest]

Thanks for replying Ken.

I set the "HasFieldNames" to False, and I removed the Autonumber field in
the table, but I'm still receiving Run-time error '13': type mismatch. Any
thoughts on why this might be happening?

 

[Ken Snell \(MVP\)]

Do any data get imported? Try changing the Range argument to
"Sheet1!"

Otherwise, post more details about the table's structure (fields, types,
etc.) and some sample data from the EXCEL file.