Unable To Read Certain fields in Linked Excel Sheet to Access

[Gary]

I am using Acc 2003, but Acc 2007 should work the same (I think).

I have linked an excel spreadsheet into my Access database. Almost all of
the fields are available and can be read.

There are a few fields that I can see in Access, but its in Excel. These
fields were using a VLookUp function in excel to get the value from another
excel worksheet.

Also the linked excel sheet has fields that I believe should be text (in
Access), but its was set to be a number. I am unable to change this.

Is there a way to resolve this issue for me ???

I also assume that if I was to read each cell in the excel sheet and convert
it into text files using VBA code, that should resolve the issue.

Does someone have any suggestions for me ???


Thank You,

G

 

[Tom van Stiphout]

Hi Gary,
That's not my experience. I just created a workbook in Excel2007 that
included a column with a vlookup function, and it worked like expected: the
data was visible in the Access-attached table.
Rather than going through the pain of exporting to text, consider
*importing* the data rather than linking to it.
Access determines the datatype of a column by scanning only the first few (I
think 8) rows. If those are numeric, and further down there is text, indeed
you will get the wrong data type. Perhaps you can sort your data differently,
or include a fake top row that is there only during linking and is later
removed.

Tom van Stiphout
Microsoft Access MVP