Importing Excel spreadsheet

  • Thread starter Thread starter MarianneZ
  • Start date Start date
M

MarianneZ

I'm trying to import an Excel 2003 spreadsheet into an Access 2007
database using DoCmd.TransferSpreadsheet. The destination database
contains all text fields. I get an error trying to import the value
"<2" from the spreadsheet. I tried to specify
acSpreadsheetTypeExcel12, thinking that might be the problem. But I
got an error saying acSpreadsheetTypeExcel12 was not defined. Ditto
11.

Any suggestions?

Marianne
 
Marianne

If, by "value", you mean a number, then ">2" is definitely not a "value".
At a minimum, ">2" is a range. As far as Access is concerned, ">2" is a
text string.

What data type are you trying to import this "value" into in your Access
table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Marianne

If, by "value", you mean a number, then ">2" is definitely not a "value".
At a minimum, ">2" is a range. As far as Access is concerned, ">2" is a
text string.

What data type are you trying to import this "value" into in your Access
table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Marianne

If, by "value", you mean a number, then ">2" is definitely not a "value".
At a minimum, ">2" is a range.  As far as Access is concerned, ">2" is a
text string.

What data type are you trying to import this "value" into in your Access
table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

I'm trying to import into a text field in the Access table. Actually I
mistyped. It's a < sign, not a >. All the Access table fields are
text. Most of the Excel file cells contain numbers, but there are
other instances with more text that seem to import just fine.

TIA,
Marianne
 
Marianne

If, by "value", you mean a number, then ">2" is definitely not a "value".
At a minimum, ">2" is a range.  As far as Access is concerned, ">2" is a
text string.

What data type are you trying to import this "value" into in your Access
table?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

I'm trying to import into a text field in the Access table. Actually I
mistyped. It's a < sign, not a >. All the Access table fields are
text. Most of the Excel file cells contain numbers, but there are
other instances with more text that seem to import just fine.

TIA,
Marianne
 
What if you build the table first and then set the field type to
text? Then you can import any combination of text/numbers.
 
What if you build the table first and then set the field type to
text? Then you can import any combination of text/numbers.
 
What if you build the table first and then set the field type to
text?  Then you can import any combination of text/numbers.
Click to expand...

I did define the table first and I did define the fields as text.

Marianne
 
What if you build the table first and then set the field type to
text?  Then you can import any combination of text/numbers.
Click to expand...

I did define the table first and I did define the fields as text.

Marianne
 
Try acSpreadsheetTypeExcel9 as the version type.
Click to expand...
Yes, I discovered by trial and error that specifying type 9 did not
cause an error in itself. But I wondered if the error I get uploading
the Excel file is because it was from a later version of Excel?

Marianne
 
Try acSpreadsheetTypeExcel9 as the version type.
Click to expand...
Yes, I discovered by trial and error that specifying type 9 did not
cause an error in itself. But I wondered if the error I get uploading
the Excel file is because it was from a later version of Excel?

Marianne
 
Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and 2003,
so it's the correct one to use when importing such a workbook version.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Try acSpreadsheetTypeExcel9 as the version type.
Click to expand...
Yes, I discovered by trial and error that specifying type 9 did not
cause an error in itself. But I wondered if the error I get uploading
the Excel file is because it was from a later version of Excel?

Marianne
 
Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and 2003,
so it's the correct one to use when importing such a workbook version.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Try acSpreadsheetTypeExcel9 as the version type.
Click to expand...
Yes, I discovered by trial and error that specifying type 9 did not
cause an error in itself. But I wondered if the error I get uploading
the Excel file is because it was from a later version of Excel?

Marianne
 
Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and 2003,
so it's the correct one to use when importing such a workbook version.
--
Click to expand...

No, the data has never imported correctly. That was why I was trying
different spreadsheet types.

Say, for future reference, is there a different acSpreadsheetTypeExcel
number for Excel 2007?

I appreciate your help.

Marianne
 
Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and 2003,
so it's the correct one to use when importing such a workbook version.
--
Click to expand...

No, the data has never imported correctly. That was why I was trying
different spreadsheet types.

Say, for future reference, is there a different acSpreadsheetTypeExcel
number for Excel 2007?

I appreciate your help.

Marianne
 
acSpreadsheetTypeExcel12 is the value for EXCEL 2007.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and
2003,
so it's the correct one to use when importing such a workbook version.
--
Click to expand...

No, the data has never imported correctly. That was why I was trying
different spreadsheet types.

Say, for future reference, is there a different acSpreadsheetTypeExcel
number for Excel 2007?

I appreciate your help.

Marianne
 
acSpreadsheetTypeExcel12 is the value for EXCEL 2007.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Do the data import when you use acSpreadsheetTypeExcel9?
acSpreadsheetTypeExcel9 is the version matching EXCEL 2000, 2002, and
2003,
so it's the correct one to use when importing such a workbook version.
--
Click to expand...

No, the data has never imported correctly. That was why I was trying
different spreadsheet types.

Say, for future reference, is there a different acSpreadsheetTypeExcel
number for Excel 2007?

I appreciate your help.

Marianne
 
Ken -- I am hoping you are still monitoring this post thread. I just posted
in another thread hoping to get help...

I have a predefined table with a Memo field, am importing into Access 2007
from Escel 2007 using acSpreadsheetTypeExcel12.

When my import is done, the Datatype Memo is still assigned to the field in
question, but the value from the 34th row in Excel, which is 1,000 characters
long, is still truncated to 255 characters.

Any suggestions? Is this possibly a bug? Any suggestions on how to get
around it? Short of having all my users "tweak" their registry entries...
 
Back
Top