Importing Excel spreadsheet into Access (1 Viewer)

[shay]

I am writing a VB5 application which imports an Excel 97 worksheets into an Access 97 database.

The code works fine except for one field on one worksheet. The first hundred or so values for this field is Text, thereafter it is numeric. After running the VB application, the imported table correctly contains the Text values for the 100 records but then displays "#Num!" instead of the numeric values (the field data type is defined as Text, by the way).

If I open the Access database and manually import the worksheet into a new table, it works fine.

The code I am using to import the data follows:


Dim MyTdf as TableDef, Table as String

Table = "B8 Results"
Set MyTdf = MyDB.CreateTableDef(Table)
MyTdf.Connect = "Excel 8.0; database=" & MySpreadsheet
MyTdf.SourceTableName = Table
MyDB.TableDefs.Append MyTdf

(MyDB & MySpreadsheet defined elsewhere)


Has anyone come across this problem before? Any clues at all?

shay

 

[Mario]

Is your field in Excel all text? Or is it a mix of text and number... Maybe it can help if you format your column as text, even the numeric value. It is just an idea here, I don't know if it will work.

 

[shay]

The field in the Excel worksheet contains both text and number and is formatted as Text.

shay

 

[Mario]

Dim MyTdf as TableDef, Table as String

What if you defined those variable as Variant instead? Or put a single quote (') in front of your numerical values in excel...