VBA to overwrite existing file without warning

Hi

I have some vba which saves mutiple files however if there's a file already there with the same name the code stops with an error. It doesn't matter if these files are overwritten. Here is the code at the moment.

wb.Close savechanges:=False
destWb.SaveAs "C:\xxxxxx
destWb.Close False

Many thanks in advance.

Myob Report Guru

Sorry it stops with a vba error code which says something like 'file already exists' along with the error code 58. When I pressed debug it highlighted the code which I put above. Hope this helps.

Delete the file first if it exists:

wb.Close savechanges:=False
If dir$("C:\xxxxx") <> "" then kill "C:\xxxxx"
destWb.SaveAs "C:\xxxxxx
destWb.Close False

I'm getting a compile error when I tried to add your code. I've put more info in that might help. Thanks for your help.

Please Login or Register  to view this content.

The idea is to delete the exact file you are trying to save over the top of. That way there is no error of it already existing.
Just check to see if the file exists using Dir$, then use the Kill command to delete it if it is there. So in this example, I use Dir$ to see if the file exists. If it does, use Kill to delete it. Then when you do the SaveAs there is no error because the file will not exist since it's been deleted. i.e...

wb.Close savechanges:=False
If Dir$("C:\Users\xxxx\Sales Reports\" & CreateFileName(destWks.Range("a4").Value)) <> "" Then Kill "C:\Users\xxxx\Sales Reports\" & CreateFileName(destWks.Range("a4").Value)
destWb.SaveAs "C:\Users\xxxx\Sales Reports\" & CreateFileName(destWks.Range("a4").Value)
destWb.Close False

Thanks for your reply. It looks like that problem is fixed but now I have another one. See below. The problem is on these 2 lines -
Name folderPath & "\" & fileName As _
"C:\Users\xxxx\Sales Report Raw Data Actioned\" & fileName

Please Login or Register  to view this content.

I think we have to delete and replace any duplicates in folder as well. This is the address where the raw data file gets moved too. Thanks again.

Your problem is starting to get a little vague. It looks like you are using a couple of custom functions that I have no idea what they are doing. I do know that the file you are trying to rename can not already exist in the same folder, so you could try this. Same as before, this will delete the file if it already exists before moving or renaming a file to that name.

Please Login or Register  to view this content.

Thank you so much it now works without any problems. Just to put you in the picture for this project I'm sent sales figures for multiple stores all in one spreadsheet. I get one for every week that covers 7 days at all stores. Another colleague wrote the original code but now he's tied up with other work and I was under pressure to get this sorted for my bookkeeping staff. The whole project is to create new spreadsheets with each store on different tabs for each week and then move them to another folder. The original raw data files are moved also to another folder. The problem came when raw data files covered previous periods that we had already processed so hence we had to delete the older version of raw data files and our completed sales files. Thanks again for all your help. It has been really appreciated.

You're welcome! I'm glad you got it working!