Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help With Overwriting a File In Excel VBA

0

Hey guys. I am trying to use VBA to export all worksheets of a file into their own excel file which I plan to send out on a monthly basis. I am wanting to do a SaveAs to overwrite the existing files without the prompt when I save them to the same folder each month. I will not be changing the file location or wanting the file name changed, just the file overwritten. It's essentially mixing the two youtube videos that I found online called "VBA SaveAs to Overwrite Existing File without Prompt" and "Fast Export All Worksheets into Their Own Excel File". The code does not seem to be working when I use the Application.DsiplayAlerts code in combination with saving multiple worksheets into their own file. That is I don't know how to make it work and I'm assuming I need to make a little change in the code to accomadate this combination. Can someone please help me with specifics in what to change to make this work? To be clear I got both of these coding tasks to work individually, it is just the combination of tasks is what I'm needing. I get an error 1004 Method 'SaveAs' of object'_workbook' failed error. Thanks for any help!

Sub WorksheetExport()

Dim ws As Worksheet
Dim wsDash As Worksheet
Dim wbToSave As Workbook
Dim filepathToSave As String

Set wsDash = Worksheets("Dashboard")

filepathToSave = "RandomFileNameJustForThisScreenShot\"

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> wsDash.Name Then
Application.DisplayAlerts = False

ws.Copy

Set wbToSave = ActiveWorkbook

wbToSave.SaveAs _
Filename:=filepathToSave & wbToSave.Worksheets(1).Name & ".xlsx", _
FileFormat:=51

Application.DisplayAlerts = True

wbToSave.Close True


End If

Next ws

End Sub
Answer
Discuss

Discussion

Did you try my answer? 
John_Ru (rep: 6102) Nov 23, '22 at 3:42 am
Add to Discussion

Answers

0
Selected Answer

Hi and welcome to the Forum.

You just need to move Application.DisplayAlerts lines outside the loop which saves the files.

In the modified code below, I've done that, temporaily disabled screenupdating (so the new workbooks don't "flash up" as they are created) but given a message once the files have been saved. Changes are in bold (plus the reminder to correct filepathToSave before running the macro):

Sub WorksheetExport()

Dim ws As Worksheet
Dim wsDash As Worksheet
Dim wbToSave As Workbook
Dim filepathToSave As String
Dim n As Long

Set wsDash = ThisWorkbook.Worksheets("Dashboard")
filepathToSave = "RandomFileNameJustForThisScreenShot\" ' change this to a real location

Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> wsDash.Name Then

        ws.Copy

        Set wbToSave = ActiveWorkbook

        wbToSave.SaveAs _
        Filename:=filepathToSave & wbToSave.Worksheets(1).Name & ".xlsx", _
        FileFormat:=51

        wbToSave.Close True
        'add to saved counter
        n = n + 1
    End If

Next ws

Application.DisplayAlerts = True
Application.ScreenUpdating = True
' tell user what happened
MsgBox n & " files saved to " & filepathToSave

End Sub

Hope this fixes things for you. If so, please remember to mark this answer as Selected.

Discuss

Discussion

Thanks for the quick reply! Unfortunately this didn't solve the overwriting issue. I'm still getting the Run-time error '1004' Method 'SaveAs' of object_ workbook failed. The macro ran fine when there were no files in the folder but then I ran it again to overwrite and it gave me the error and looks like it only overwrote the first file and stopped there (as well as leaving it open). I'm wondering if there is something else I'm missing. I made all the changes you mentioned as well as changing my location back to the real one I was using.
excellingatlife (rep: 2) Nov 18, '22 at 10:13 am
Firstly, you don't have to "make the changes" manually- just click "Select all" at the top of the code and it will be put in your clipboard.Just paste it over your existing module. That saves time and avoids any transcription errors too

Secondly I didn't get that problem. If I used filename illegal characters (e.g. > in the sheet names) I got a different 1004 error (advising that as an option).

Could be an issue with ActiveWorkbook. Here's an approach I've used before (don't forget to correct the path, ending it with a backslash \ (changes in bold again). See next dicussion item....
John_Ru (rep: 6102) Nov 18, '22 at 11:43 am
Sub WorksheetExport()
 
Dim ws As Worksheet
Dim wsDash As Worksheet
Dim wbToSave As Workbook
Dim filepathToSave As String
Dim n As Long, NewWb As Workbook
 
Set wsDash = ThisWorkbook.Worksheets("Dashboard")
filepathToSave = "RandomFileNameJustForThisScreenShot\" ' change this to a real location
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
 
For Each ws In ThisWorkbook.Worksheets
 
    If ws.Name <> wsDash.Name Then
        ' create a workbook
        Set NewWb = Workbooks.Add
        'copy sheet to new file
        ws.Copy Before:=NewWb.Sheets(1)
        NewWb.Sheets(2).Delete

        ' save and close new file
        NewWb.SaveAs _
        Filename:=filepathToSave & ws.Name & ".xlsx", _
        FileFormat:=51
        
        NewWb.Close True
        
        'add to saved counter
        n = n + 1
    End If
 
Next ws
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
' tell user what happened
MsgBox n & " files saved to " & filepathToSave
 
End Sub


Please let me know if that works for you. If so, I'll add to my Answer and hope you will then mark it Selected.
John_Ru (rep: 6102) Nov 18, '22 at 11:43 am
Glad that worked for you. Thanks for selecting my answer. 
John_Ru (rep: 6102) Nov 23, '22 at 4:10 pm
This second one didn't work for me, I get the runtime 1004. I gave your first response the selected because there is a slight workaround I implement. I can just grab the files and throw them in an archive folder within that folder before running the macro and don't have to worry about it. For some reason the first one you gave me ran and actually overwrote the files and then I rerun the same macro 2 mintues later to overwrite them again - same code no changes and then it breaks and gives me a 1004 error. It like works sometimes and then malfunctions the majority of the time. I did make sure to check the names of the tabs so they didn't have illegal characters but that wasn't the problem. Thank you for the help! 
excellingatlife (rep: 2) Nov 23, '22 at 4:49 pm
Understood and sorry about that - not sure why it misbehaves (since I had no such errors, having tried it several times). 
John_Ru (rep: 6102) Nov 23, '22 at 5:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login