Re: Importing Excel, more data type problems

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.

--

Ken Snell
<MS ACCESS MVP>

"James" <James@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F1563F14-BC05-4179-A06F-DC9FF16064B0@xxxxxxxxxxxxxxxx
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)" wrote:

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
TransferSpread*** 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 TransferSpread*** will try to import data into that field from
the spread*** 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.


--

Ken Snell
<MS ACCESS MVP>




"James" <James@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3414F15-BC60-4D50-8CCC-C60DEA6B7307@xxxxxxxxxxxxxxxx
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.TransferSpread*** 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!





.