Hi All

I'm having trouble with #num! errors when I link to a spreadsheet. The spreadsheet is currently in use by people at work so the I dont have the option of changing the cell formats in the spreadsheet itself. The errors seem to be type mismatch errors which shoud be fairly easy to overcome if there is a method of trapping them within a query.

The basic process I am using is generate a link to the spreadsheet & then use various update & append queries to extract the data I want into my own tables. What I would like to do in the query is test if the field contains the #num! error & then perform a string conversion in it. I've tried testing it with the Iserror function but that just returns #num!.

Everything I've read so far seems to imply that this sort of problem can only be dealt with at the spreadsheet end. Hopefully it can be done through access.

the sort of thing I've got in mind is

iif(Iserror(my_var),val(my_var),my_var)

where my_var is a numeric string

if my_var returns an error then the cell contains a string, in which case take the value of the string & use that instead, otherwise return the numeric value of my_var.

Ive also tried variations of this with cverr & running the opposite test on the fields that don't produce the error.

Another thing I've notced about linking to spreadsheets is that access will link to a spreadsheet whether or not the spreadsheet is open. But if a user tries to open the spreadsheet that access has linked to their access is denied, even though the link is read only. Is there a setting within either Access or Excel that would overcome this?

Many thanks