Type Conversion Failure importing into text??

[Susan G]

I have done a URLDownloadToFile which allows me to import
a web site excel spreadsheet to be saved into an excel
spreadsheet. I then attempted to do a TransferSpreadsheet
from this spreadsheet into a table. The first time I did
it I allowed the table to be created and I got a Type
Conversion Failure on a field which was made a number in
the table, but has the value of an "N" followed by a
number (ex N010) in the excel spreadsheet. I looked at
how that row is formatted and it is as a number. So I
changed the problem field in the table to be text, but I
am still getting the same error and the value is being
set as null. When I looked at the column in the excel
spreadsheet, it was formatted as a number. How can I
still be getting this error if it is set to text? How can
I get around this since they do need the value in that
field? Please help!!
Susan G

 

[John Nurick]

Hi Susan,

The Access routine that imports Excel data doesn't allow direct control
over the types of the fields it creates, and often runs into trouble
with Excel columns that contain a mix of numeric and text values.

You usually can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in thecells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access. The little Excel macros at the end of
this message can be used to insert or remove the apostrophes.

(Just to make things more confusing, Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.



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