Importing Excel into Access

Importing Excel into Access

Post by Eric Wes » Fri, 28 Apr 2000 04:00:00



I will also post this question to another newsgroup as I am not sure if this
is the right place to ask:

I'm using Access' Docmd from VB to import an Excel 8.0 spreadsheet into a
table. It's importing, however, some data from the spreadsheet is kicking
out into an errors table. The reason being a Type Conversion Error.

I want to import EVERY field for what it is. Not based on what Access thinks
it should be!

I have tried to create a table in my code making all fields dbText then
importing, yet Access insists these values be "kicked out". Specifically,
MOST data is numeric but some data has a J or a Q before. (CPT Codes for
anyone whos curious). Even when I link the spreadsheet, I cant refer to any
nonnumeric data in code or I get a Numeric Overflow error (???).

Heres some of my code:

'Import Code:

Set AC = New Access.Application
AC.OpenCurrentDatabase PathStr & "CPT_ICD.MDB"
AC.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97 ,  "CDM",
mvarXLFile, false
AC.Quit

'I tried the above with no luck so I created a table, then ran the above
code:

Set xlCDM = db.CreateTableDef("CDM")

With xlCDM
   .Fields.Append .CreateField("F1", dbText)
   .Fields.Append .CreateField("F2", dbText)
   .Fields.Append .CreateField("F3", dbText)
   .Fields.Append .CreateField("F4", dbText)
End With

db.TableDefs.Append xlCDM

'The above didn't work, so I linked the darn thing:

Set dbXl = OpenDatabase(mvarCDMFile, False, False, "Excel 8.0;HDR=NO")

As I've said, all import or link fine. Is there any way to tell the Jet
Engine not to evaluate the datatype?

Thanks,
Eric West

 
 
 

Importing Excel into Access

Post by Metr » Fri, 28 Apr 2000 04:00:00



> I will also post this question to another newsgroup as I am not sure if this
> is the right place to ask:

> I'm using Access' Docmd from VB to import an Excel 8.0 spreadsheet into a
> table. It's importing, however, some data from the spreadsheet is kicking
> out into an errors table. The reason being a Type Conversion Error.

> I want to import EVERY field for what it is. Not based on what Access thinks
> it should be!

> I have tried to create a table in my code making all fields dbText then
> importing, yet Access insists these values be "kicked out". Specifically,
> MOST data is numeric but some data has a J or a Q before. (CPT Codes for
> anyone whos curious). Even when I link the spreadsheet, I cant refer to any
> nonnumeric data in code or I get a Numeric Overflow error (???).

> Heres some of my code:

> 'Import Code:

> Set AC = New Access.Application
> AC.OpenCurrentDatabase PathStr & "CPT_ICD.MDB"
> AC.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97 ,  "CDM",
> mvarXLFile, false
> AC.Quit

> 'I tried the above with no luck so I created a table, then ran the above
> code:

> Set xlCDM = db.CreateTableDef("CDM")

> With xlCDM
>    .Fields.Append .CreateField("F1", dbText)
>    .Fields.Append .CreateField("F2", dbText)
>    .Fields.Append .CreateField("F3", dbText)
>    .Fields.Append .CreateField("F4", dbText)
> End With

> db.TableDefs.Append xlCDM

> 'The above didn't work, so I linked the darn thing:

> Set dbXl = OpenDatabase(mvarCDMFile, False, False, "Excel 8.0;HDR=NO")

> As I've said, all import or link fine. Is there any way to tell the Jet
> Engine not to evaluate the datatype?

> Thanks,
> Eric West


From Access's help files:
=======================
Microsoft Access has assigned an incorrect data type to a field.
Microsoft Access assigns the data type for each field based on the data
in the first row it imports. For example, if a field that contains
mostly text values has a number in the first row, Microsoft Access
assigns the Number data type and then can't import the rest of the
records.

=======================

So, probably what is happening is that you have a text field and the
first row of data for that field has no alpha characters in it, causing
Access to expect a numeric field.  This happens with Excel files.

Several ways around it.  Here's a few:
1) change the data in the first row to include a text character in each
field and change it back after importing it.
2) export the data as .csv, create and save an import specification in
Access and use it to import the data.
3) import from within Access, manually changing the datatype to text for
each field.
4) using automation, select and copy the range you want from Excel and
paste it into an existing Access table.
5) save as CSV and use DAO from within VB with a INI file or two (see KB
Q230265).

I've mostly used #2 within Access.  1, 3 and 4 are pretty much one-time
deals, in my opinion.  I think #5 is the your best long-term option in
VB.  I've looked at the sample vbp for #5, and it demonstrates several
ways to accomplish the import using a text file for data.

Hope somebody else has better options for you,

Metra

 
 
 

1. Importing Excel into Access

Question:

What's the best (read: easiest) way to import an excel spreadsheet of
"known" format
into Access using a VB5 front end, then save it as part of the Access
database.

I can easily open it using the data control by just setting the DataBaseName
to the *xls file,
but how could I just as easily turn around and save it to the *.mdb file?

Any advice?

Thx!

Dan

2. Data Administration

3. import Excel to Access 97 w/ VB

4. Running a report or form from another database

5. Importing Excel into Access

6. Passing the value of a select to a variable

7. import excel to Access 97 by using adox

8. Error saving table restructure

9. Simple question importing Excel to Access

10. Import data to SQL 6.5 from Excel spreadsheet and / or Access DB

11. importing an excel/access table to a SQL 7.0 table

12. Import Excel sheet into Access table with VB

13. Importing data from Excel to Access using VB???