Import Excel SSheet Columns as Text Only

[Mike Thomas]

In Access 2003, we are importing Excel spreadsheets and are having a problem
with Access deciding that columns should be numbers rather than strings.
The spreadsheet is imported with the command

DoCmd.TransferSpreadsheet acImport, 0, "xImport", "d:\example.xls", True

I would like to find a way that Access would simply set each column of
xImport as a text column, then I can further process the data from there.

An example of the problem is an Excel column which has a number for the
first hundred rows, then an entry in the form of '03' where the zero is
significant. Do I need to make the table first, then read the Excel file
one row at a time, testing the type of each cell?

Many thanks
Mike Thomas

 

[strive4peace]

Hi Mike,

This is a frustrating problem! Even if you have an import specification
defined, Access seems to analyze the first 30 or so rows and determine
the data type from what it sees...

for this reason, I often make a "dummy" first row with text in every column


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Mike Thomas]

Thanks Crystal,

Yes, I've never solved it. I think the other way around the issue might be
to write a macro or VBA snippet in Excel which would simply append an
apostrophe to each value in each excel cell.

Thanks
Mike Thomas

 

[strive4peace]

Hi Mike,

you're welcome

that will work too... but the single quote mark in the beginning of the
cell may come into Access too... then you will need to run an update
query to strip them -- but at least the data will get there!



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[John Nurick]

Hi Mike and Crystal,

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

....or for the gory details, see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and
http://support.microsoft.com/?id=257819

 

[strive4peace]

thanks, John!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*