Saving & Overwriting existing Excel files

[billHassall]

I am using a macro to run and save queries into existing Excel files. I get a prompt for every file, there are nine, asking if I want to overwite the existing file and I am asked to answer yes or know.

I have had a look at similar threads around this and tried using Setwarning to off as the first line of the macro. This worked OK for all of the delete query messages which stopped, but the overwrite message prompt still appears. I even tried putting the Setwarning before every Outputto line, but this didn't work either.

Is it possible to stop the save as prompts using Macro commands ?

Thanks

Bill

 

[billHassall]

I am using Access 2003 and Excel 2007

 

[vbaInet]

That is a Windows prompt, not an Access prompt.

Why not delete the file if it exists, and name the new file the name of the deleted file.

 

[billHassall]

Thanks, only problem is that the output files are then referenced out to other files that pick up their data from them, which may then end up with invalid references. Will try it with one file and see what happens


Bill

 

[boblarson]

Thanks, only problem is that the output files are then referenced out to other files that pick up their data from them, which may then end up with invalid references.

If you are replacing a workbook with the same name then it matters not if you delete it first or overwrite it. The references will still refer to the same named workbook.

To delete if it exists you can use:

Dim strFile As String
 
strFile = "C:\SomeFolder\SomeFile.xls"
 
If Dir(strFile) <> "" Then
   Kill strFile
End If
 
' then do your save

 

[billHassall]

Thanks, only ever used the macro function, so will be interesting having a go at modding the actual code

Bill

 

[billHassall]

I saved the macro as a module and then created a macro to run the module, when running the module I did not get any Yes/No prompts, so the problem was solved

Thanks for your help

Bill