Stop pop-up boxes during Macros

[User Name:]

Hi Everyone,

Quick question...

I run daily macros which run queries then exports them, the problem I'm having is as this is a daily export the existing file from the previous day is still in the saved location, so I get a pop-up box asking if I want to overide the previous version (xlsx files).

I have looked into SetWarnings but this has not helped, can anyone here advise what is best to do?

thanks.

UN

 

[Isskint]

If you definitely do not need the existing file, then use the Kill statement. Use it like;

Kill filename&path

 

[User Name:]

Brilliant, thanks.

Would I use that in VBA? If so, how would I phrase it?

 

[Isskint]

Yes it is VBA.

Open the Visual Basic Editor (VBE) press ALT + F11
Right click the name of your database in the left pane and select Insert module
In the right pane type;

Public Function DeleteExisitngFile()
Kill "C:\Users\Me\Documents\test.txt"
End Function

Obviously C:\Users\Me\Documents\test.txt needs to be the path and name of your file
Save this (you will be prompted to name the new module you just created, call it whatever you like - mduFunctions perhaps)
Now open your export macro.
insert a line before the export line and select RunCode as the action.
In the action arguments box at the bottom type DeleteExisitngFile () as the function name. Save your macro and hey presto it should sort the problem

!!!!!!CAUTION!!!!!!! Kill will delete anything you tell it too. ENSURE your path and filename are correct or you may lose something else. Filename is very important or you will delete the entire folder (and any subfolders) at your target. EG in my example C:\Users\Me\Documents would be deleted if i miss out the filename!!

 

[User Name:]

Thank you so much!