TransferSpreadsheet method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, I am having the exact same problem as this gentelman below, although no-one has replied to his posting. Does anyone know how to stop Access from automatically applying arbitrary data types?

I am using the TransferSpreadsheet method to import about 7000 records, and for some of the fields, it is turning the data type from Text into Number, where I would like it to remain Text.

I would like to avoid modifying the source Spreadsheet, and avoid editing the Registry.

Thanks in advance for any help

CHRIS


Hi yall,

I am using the TransferSpreadsheet method in Access 97 to
export a table to Excel 97. My problem is that some data
fields have strings such as 3-3 or 1-3 etc. etc. and Excel
wants to automatically format those fields as dates when
they actually are not. Is there a way I can stop Excel
from automatically formating this type of data as a date
when using the TransferSpreadsheet method? Thanking you in
advance..
 
Can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a text
string?

Or, put in the first row an "alphanumeric" value so that ACCESS sees it and
knows that not all values are dates.

If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>

Chris said:
Hi there, I am having the exact same problem as this gentelman below,
Click to expand...
although no-one has replied to his posting. Does anyone know how to stop
Access from automatically applying arbitrary data types?
I am using the TransferSpreadsheet method to import about 7000 records,
Click to expand...
and for some of the fields, it is turning the data type from Text into
Number, where I would like it to remain Text.
 
Thanks Ke

I went with your second idea, to put in an alphanumeric row at the begining of the excel spreadsheet

When I import it into Access, I just run a Delete statement to remove the dummy row

Everything seems to work fine now, although I still wish I had more control over the way Access assigns field types

Regard

CHRI


----- Ken Snell wrote: ----

Can you go into EXCEL and insert a ' character in front of each of th
values in the column? That will tell ACCESS to treat the value as a tex
string

Or, put in the first row an "alphanumeric" value so that ACCESS sees it an
knows that not all values are dates

If nothing else works, you can use Automation (via VBA code) to open th
EXCEL file and read the spreadsheet cell by cell and write the values into
recordset that is based on the target table

--
Ken Snel
<MS ACCESS MVP

Chris said:
Hi there, I am having the exact same problem as this gentelman below
Click to expand...
although no-one has replied to his posting. Does anyone know how to sto
Access from automatically applying arbitrary data typesand for some of the fields, it is turning the data type from Text int
Number, where I would like it to remain Text
 
Back
Top