Auto-fill File Names When Saving

[TheStu]

At work I fill out similar forms in excel dozens of times each day and then name them uniquely based on 3 different pieces of data that are available in the cells of the spreadsheet.

Is there anyway to setup a macro or something that I could invoke that would open the save dialog box with the file name pre-filled with things like "<Cell E1> <Cell B1> <Cell A1>"?

Ideally it would look like;

ABCD1234 YYYY MM DD WXYZ.xlsx And then I can double check it (or edit it which is sometimes necessary) and save.

 

[Dulanic]

Sure, just enter the vba code like so:

ActiveWorkbook.SaveAs "C:\" & Range("B2") & "\" & Range("B1") & ".xlsm"

If B2 was dummy and B1 was test1 that would save the file as C:\dummy\test1.xlsm

Just keep in mind it must be saved as a .xlsm file not .xlsx since it would have a macro built in. Or save the macro in a separate file and just call it from the file your saving.

See http://www.rondebruin.nl/saveas.htm for more info.

 

[TheStu]

Sure, just enter the vba code like so:

ActiveWorkbook.SaveAs "C:\" & Range("B2") & "\" & Range("B1") & ".xlsm"

If B2 was dummy and B1 was test1 that would save the file as C:\dummy\test1.xlsm

Just keep in mind it must be saved as a .xlsm file not .xlsx since it would have a macro built in. Or save the macro in a separate file and just call it from the file your saving.

See http://www.rondebruin.nl/saveas.htm for more info.

Ok.. um, how do I do the stuff with the VBA? I have only limited experience with Macros. I know that you can do fancy stuff with VBA in excel but...

 

[Dulanic]

OK first I am guessing you are using Excel 2007/2010 so I recommend turning on the developer section of the ribbon. You can do so by going into Excel Options and click "Show Developer tab in the Ribbon".

Once you do this you can go to the developer tab, click Macros then click in macro name, then click new and paste the code.... if you tell me the data layout you want in the name I can write it more specifically. Make sure the folder already exists.... paste the code. I made slight changes to make it less prone to errors.

Keep in mind, excel can not create folders so the path must already exist. Alse the _ just wraps the code to the next line so it looks right and you don't need to scroll to read code.

Sub SaveFile()

ActiveWorkbook.SaveAs Filename:="C:\" & Range("B2") & "\" & Range("B1") _
& ".xlsm", FileFormat:=52, CreateBackup:=False
End Sub

You can then run said macro directly from the same developer tab. Also you can do anything you wish to the file name. You could add say & Format(Date,"yy") & in the middle to add the 2 digit year or "yyyy" for the 4 digit... if you you wanted it laid out like you said you could do...

"ABCD1234" & Format(Date, "yyyy mm dd") & "WXYZ" & ".xlsm"

This would result in "ABCD12342012 02 24WXYZ.xlsm". Just mess around with the range and other thing. If ABCD1234 was in A1 then replace that with Range("A1").

 

[Dulanic]

Forgot you wanted to verify the filename first... you could do something like this:

Sub SaveFile()

Dim Answer As String
Dim MyNote As String
Dim SavePath As String

SavePath = "C:\test\ABCD1234" & Format(Date, "yyyy mm dd") & "WXYZ" & ".xlsm"

    MyNote = "Is " & SavePath & " the right filename?"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

    If Answer = vbYes Then
        ActiveWorkbook.SaveAs Filename:=SavePath, FileFormat:=52 _
        , CreateBackup:=False
        MsgBox "File was saved"
    Else
        MsgBox "File was not saved"
    End If
End Sub

This would prompt you with the filename & path prior to saving asking if it is correct.

 

[TheStu]

Forgot you wanted to verify the filename first... you could do something like this:

Sub SaveFile()

Dim Answer As String
Dim MyNote As String
Dim SavePath As String

SavePath = "C:\test\ABCD1234" & Format(Date, "yyyy mm dd") & "WXYZ" & ".xlsm"

    MyNote = "Is " & SavePath & " the right filename?"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

    If Answer = vbYes Then
        ActiveWorkbook.SaveAs Filename:=SavePath, FileFormat:=52 _
        , CreateBackup:=False
        MsgBox "File was saved"
    Else
        MsgBox "File was not saved"
    End If
End Sub

This would prompt you with the filename & path prior to saving asking if it is correct.

This is all great, thanks a bunch. Yes, Excel 2007. I'll play around with it a bit today when I have some downtime, see if I can't make it happen how I want.