Turn off save confirmation in Excel automation through Access97

[RottPaws]

I have a database that uses information from an Excel spreadsheet as a linked table for data.

The owner of that spreadsheet (on the network) recently made some changes to it and now Access says it is in "Not in the expected format", so it won't link to it.

I found that if I open the file in Excel and save it to my HD, Access will then link to the saved file. So I'm trying to automate having Excel open the file and save it locally.

I'd like to make it work so the person running the procedure doesn't have to do anything once they start it running.

It works great the first time, but then on subsequent attempts, a confirmation message box pops up saying the file already exists and asking if you want to replace it. I tried turning off warnings, but it doesn't stop this confirmation for saving the file.

Here's the code I have now:

Dim AppXL As New Excel.Application

DoCmd.setwarnings False

'Open, save, and close the spreadsheet
AppXL.Workbooks.Open ("\\longpathname\opnordsnb.xls&quot
AppXL.ActiveWorkbook.SaveAs "C:\Temp\opnordsnb.xls", xlExcel5
AppXL.Workbooks.Close

DoCmd.setwarnings True

Any ideas?

_________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[KellyK]

Add AppXL.DisplayAlerts = False

to the beginning of your Excel code

You may want to set it back to True at the end. Kelly

 

[RottPaws]

Thanks a bunch! _________
Rott Paws

...It's not a bug. It's an undocumented feature.