Export Format not avaiable

[Phil Smith]

"The Format in which you are attempting to output the currentobject is
not available."

I hate access sometimes. It just get's weird, throwing bogus error
messages all over the place.

I have about 30 seperate queries that I run out to spreadsheets via
macro. I have already found out that things can get all screwed up,
(meaning it bombs) when those spreadsheets already exist, so the first
thing I do is delete the existing spreadsheets, then let them rip.

I run into this every once in a while: 20 or so queries into my macro, a
query will fail with the above error, referring to Excel 97-2003
workbook. It is always query specific. Once that query gets stuck like
that, that is all she wrote. The query runs fine, just not out to a
spreadsheet. I have usually been able to fix this by changing the
output format to an older version, although at some point, Access will
bitch about THAT format being unavailable, so I go BACK to the original
one which works again.

Now I have one of these which is refusing to operate under that
particular set of rules. I can export it manually to that format no
problem, but onyl when I attempt to export it via macro,
(transferspreadsheet or Output TO, makes no difference,) it bombs.
'
Any Ideas?

Phil

 

[Kou Vang]

Why don't you check to see if the spreadsheet exists, if so, delete then run
the query?

ie:

for each file in dir.folder
If file.name = "somefile.xls" Then
Kill "path\somefile.xls"
End if

Run Qry
next file