VBA - Saving

[plost33]

I am wondering if anyone knows how i might be able to have VBA save my file in its existing location with its current name as well as save it a second time in another location with a diffrent name whenever the "save" icon, the little blue disk, is clicked on the upper left hand corner in Excel 2010?

I am basic with VBA and i am not sure how this code might look. Hope someone can assist.

 

[plost33]

here is the code i have now:

Sub SaveTwice()
Dim ProjectNumber As String
Dim ProjectName As String
Dim SaveLocation As String
Dim XXX As String
ProjectNumber = Sheets("Budget").Range("A3").Value
ProjectName = Sheets("Budget").Range("A4").Value
SaveLocation = "C:\Users\tphythian\Documents\WWCI Cost Tracking Module\Archive"
XXX = Format(Date, "[$-409]ddmmmyyyy;@")
With ActiveWorkbook
        .Save
        .SaveAs SaveLocation & ProjectNumber & " - " & ProjectName & " - " & XXX & ".xlsx"
End With

End Sub
[\code]
 
 
I believe there is a problem with the .saveas line but i can't figure it out.  i am getting an error message that says:
 
 "this extension can not be used with the selected file type.  change the file extension in the file name text box or select a diffrent file type by changing the Save as type."
 
 
I am also trying to have the code above run whenever the "save" button is clicked on the top ribbin bar in Excel 2010.  To try and get it to execute when that button is clicked i am using the following code:
 
[code]

Private Sub workbook_save()
Application.******* "save", "savetwice"
End Sub
[\code]
 
 
 
anyone have suggestions on getting this up and running?

 

[John_w]

Looks like you need to terminate the save folder path with a backslash. Try this (all the code goes in the ThisWorkbook module). I've used SaveCopyAs instead of SaveAs, so that the active workbook keeps its original name.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Save_Copy
End Sub

Sub Save_Copy()
    Dim ProjectNumber As String
    Dim ProjectName As String
    Dim SaveLocation As String
    Dim XXX As String
    ProjectNumber = Sheets("Budget").Range("A3").Value
    ProjectName = Sheets("Budget").Range("A4").Value
    SaveLocation = "C:\Users\tphythian\Documents\WWCI Cost Tracking Module\Archive\"
    XXX = Format(Date, "[$-409]ddmmmyyyy;@")
    With ActiveWorkbook
        .SaveCopyAs SaveLocation & ProjectNumber & " - " & ProjectName & " - " & XXX & ".xls"
    End With
End Sub

My file extension in the code above is .xls because I'm using Excel 2003. For Excel 2010, try a manual Save As with the macro recorder running and look at the code generated to see what file extension it uses.

 

[plost33]

is that code saving it two times? I want to save it once in its current location and another time in the folder the code shows.

 

[John_w]

Yes, the code is saving it twice. The Workbook_BeforeSave is called when the disk save icon is clicked and saves it automatically in its current location and file name, and it calls Save_Copy which saves it in the specified folder and file name.

 

[plost33]

John thanks for all the help. it is working great!

 

[VegasUser]

John w provides an elegant solution to the save twice, cudos. John w's solution fixed another error in your original code. You were trying to save VBA code in a ".xlsx" Excel file extension. As far as I can tell, the ".xlsx" extension does not allow macros (VBA code) to be saved with the worksheet. John w's code changed the extension to "xls", making it backward compatible with Excel 97-2003, also removing the error you reported.