Re: Importing Excel, more data type problems
- From: "Ken Snell \(MVP\)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 16 Jul 2006 22:02:21 -0400
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.
--
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.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!
.
- Follow-Ups:
- Re: Importing Excel, more data type problems
- From: James
- Re: Importing Excel, more data type problems
- Prev by Date: Re: Access Email Alerts
- Next by Date: Re: Importing Excel, more data type problems
- Previous by thread: Re: Access Email Alerts
- Next by thread: Re: Importing Excel, more data type problems
- Index(es):
Relevant Pages
|
