Importing Excel spreadsheet

[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

 

[Jeff Boyce]

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

 

[Jeff Boyce]

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

 

[MarianneZ]

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

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

 

[MarianneZ]

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

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

 

[Piet Linden]

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

 

[Piet Linden]

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

 

[Ken Snell MVP]

Try acSpreadsheetTypeExcel9 as the version type.

 

[Ken Snell MVP]

Try acSpreadsheetTypeExcel9 as the version type.

 

[MarianneZ]

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

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

Marianne

 

[MarianneZ]

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

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

Marianne

 

[MarianneZ]

Try acSpreadsheetTypeExcel9 as the version type.

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

 

[MarianneZ]

Try acSpreadsheetTypeExcel9 as the version type.

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

 

[Ken Snell MVP]

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.

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

 

[Ken Snell MVP]

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.

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

 

[MarianneZ]

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.
--

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

 

[MarianneZ]

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.
--

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 Snell MVP]

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.
--

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 Snell MVP]

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.
--

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

 

[Bill Sturdevant]

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...