Using Excel 2000 macro on Excel 97 - avoid save prompt 1

[CindyK]

I created this with Excel 2000 and am trying to use it in Excel 97. It works, but gives me a message box stating the file was created in a later version of Excel and that if user clicks Yes to save they might lose formatting.

Even after we say YES, save it in the old format, it still keeps coming up with the save message.

I added this line to supposedly get around this, but it did nothing -
Application.DefaultSaveFormat = xlExcel9795

The basic code is as follows:
' I'm leaving out the selection code
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Sheet1&quot.Select
ActiveWorkbook.Save

Works fine with 2000. Works fine with Excel 97 too - it gets the query I need (this is an Access query for what it's worth - using a dqy file, Access 97.) But I want the user to be able to just close out of the files without being prompted to save even though the save happens in the background. I shouldn't put code in the auto_close for there will be times the user will be going in making changes after the data dump and I want the file to work "normally" when they just open it in a routine manner and aren't working with the macro portion.

Every time we run the macro on an Excel 97 machine, as it hits the line ActiveWorkbook.Save it tells the user that the file was created in later version. (Crazy thing is that the file itself actually WAS created in Excel 97 - but then I added the macro using Excel 2000).

Microsoft has a Q article that explains how to suppress this prompt, but it basically stops the save as well - and in this case we need the file to be saved under the same name. I've also tried doing a file, SaveAs specifying the format to be xlExcel5.

Any thoughts? TIA!

 

[DarkSun]

Try:

Application.DisplayAlerts = False

This turns off all prompts. It takes the default response as the answer to the prompt.

remember to set it to true afterwards.