link to Excel document - #Num!

[Guest]

Hello all,
I am using Access 2002 and Excel for 2002. I have a linked excel file in my
access database.
Problem: When all numbers are in the cell, the words, #Num! appear. There
are some text and some numeric values. I would like them all to be treated
as text. I have modified the format of the excel spreadsheet to text and
then deleted the linked spreadsheet and add it again, no luck.

I am unable to choose the format option in access when I create the link. I
am unable to modify the table in design mode, because it is a linked table.

Any assistance is greatly appreciated.

 

[Guest]

Although I always prefer to link to spreadsheets, there are times, and this
is one of them, that an import will work better. This is how I handle the
exact situation you are encountering:
Create a table with the field types and sizes you want to match your
spreadsheet.
Then to do the import:
1. Delete all data from the table:
CurrentDb.Execute("DELETE * FROM YourTableName;")
2. Import the spreadsheet.

Now, the data that is some text and some numbers wil all be text (provided
you defined the data type in the table as text)