VBA for bypassing the "replace file" prompt when "saving as" a file in a macro

[wilcowoods]

I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?

Any help would be gratefully accepted!

Thanks
Will

 

[Richard Schollar]

Hi

Use:

Application.DisplayAlerts = False

at the start of your code - this ensures that VBA will automatically select the default option when it processes your saveas code (which is to overwrite).

Should you wish to turn alerts back on in your rouine, set DisplayAlerts back to True.

 

[wilcowoods]

Richard - you're a gem - thanks so much! I really should put some effort into learning some VBA!

 

[soodaarti21]

Hey Richard is it possible to do with replacing an existing folder also? pls help!

 

[Richard Schollar]

It depends by what you mean "replacing an existing folder" - can you explain some more? Do you want to delete an existing folder, rename it or what?

 

[soodaarti21]

actually i am creating a folder in c drive and saving few workbooks in it thr macro..... now i want that if that folder (same name) already exist thr it should replace it automatically.

 

[Richard Schollar]

If it already exists then surely you don't need to replace it? Or am I missing something?

 

[soodaarti21]

no if it already exist i have to replace it with an empty folder first and then add new updated workbooks into it....... as it might be a case that i had run this macro last month and it contains the last month's information.... now in current month i wanted to create a new folder and add new files into it......

 

[Richard Schollar]

I think you probably want to test for the existence of the folder first, and if found, delete all the files contained within. There are several approaches you could take to do this, the following is just one:

strDirectory = "C:\Folder\YourFolderOfInterest"
If Dir(strDirectory,vbDirectory)<>"" Then
  'it exists! - now we need to loop thru the files contained within and delete them
  strFile = Dir(strDirectory & "\*.*")
  If strFile<>"" Then
    Do
      Kill strFile
      strFile = Dir
    Loop While strFile<>""
  End If
Else
  'folder does not already exist - proceed with routine
End If

Make sense?

 

[swaptul]

Is there any that <acronym title="visual basic for applications">VBA</acronym> will automatically select the non default option when it processes your saveas code.
i.e. is there any way i change default not to over wright.
Any help ??
Thanks in advance.