Importing from Excel

[Claire B]

I am importing several files from Excel into Access and
even though some of the data is numbers I want it to be
imported as text. So how would I import all the data
into Access as text fields without changing anything in
the excel file???

 

[Ken Snell]

You'd need to use VBA code to open the EXCEL file via Automation, and to
open a recordset based on the table into which the data are to be imported,
and then read each cell one at a time and write the value into the
recordset's field. Repeat read/write for each cell for a record, then start
a new record for the next row.

Be sure that the table's field(s) is/are formatted for text so that your
values will remain as text when you write them into the table.

If you can provide more info about the structure of your table and the
spreadsheet, I'm sure we can provide some code that gets you started.

 

[Ted]

You may want to look to see if you can use an append
query to append the info to an Access Table which has the
fields all defined as text. I would think that it would
allow it, but I haven't tried it. I have done something
similar with text files though.

 

[John Nurick]

Hi Claire,

If you stick an apostrophe in front of each number, it forces Excel and
Access to treat it as text (though the apostrophe isn't displayed on the
worksheet or imported into Access, or even included in the .Value and
..Formul properties of the cell). These little Excel macros add and
remove them:

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

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub