Go Back   AnandTech Forums > Software > Software for Windows

Forums
· Hardware and Technology
· CPUs and Overclocking
· Motherboards
· Video Cards and Graphics
· Memory and Storage
· Power Supplies
· Cases & Cooling
· SFF, Notebooks, Pre-Built/Barebones PCs
· Networking
· Peripherals
· General Hardware
· Highly Technical
· Computer Help
· Home Theater PCs
· Consumer Electronics
· Digital and Video Cameras
· Mobile Devices & Gadgets
· Audio/Video & Home Theater
· Software
· Software for Windows
· All Things Apple
· *nix Software
· Operating Systems
· Programming
· PC Gaming
· Console Gaming
· Distributed Computing
· Security
· Social
· Off Topic
· Politics and News
· Discussion Club
· Love and Relationships
· The Garage
· Health and Fitness
· Merchandise and Shopping
· For Sale/Trade
· Hot Deals
· Free Stuff
· Contests and Sweepstakes
· Black Friday 2013
· Forum Issues
· Technical Forum Issues
· Personal Forum Issues
· Suggestion Box
· Moderator Resources
· Moderator Discussions
   

Reply
 
Thread Tools
Old 02-23-2012, 06:47 PM   #1
TheStu
Moderator
Mobile Devices & Gadgets
 
Join Date: Sep 2004
Location: New Cumberland, PA
Posts: 11,125
Default Auto-fill File Names When Saving

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.
__________________
Quote:
Originally Posted by mfenn View Post
The 6770M can play Crysis 2, for suitably small values of play
TheStu is online now   Reply With Quote
Old 02-23-2012, 07:58 PM   #2
Dulanic
Diamond Member
 
Dulanic's Avatar
 
Join Date: Oct 2000
Location: IL
Posts: 8,844
Default

Sure, just enter the vba code like so:

Code:
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.

Last edited by Dulanic; 02-23-2012 at 08:00 PM.
Dulanic is online now   Reply With Quote
Old 02-24-2012, 12:07 AM   #3
TheStu
Moderator
Mobile Devices & Gadgets
 
Join Date: Sep 2004
Location: New Cumberland, PA
Posts: 11,125
Default

Quote:
Originally Posted by Dulanic View Post
Sure, just enter the vba code like so:

Code:
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...
__________________
Quote:
Originally Posted by mfenn View Post
The 6770M can play Crysis 2, for suitably small values of play
TheStu is online now   Reply With Quote
Old 02-24-2012, 05:27 AM   #4
Dulanic
Diamond Member
 
Dulanic's Avatar
 
Join Date: Oct 2000
Location: IL
Posts: 8,844
Default

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.

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...

Code:
"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").

Last edited by Dulanic; 02-24-2012 at 05:42 AM.
Dulanic is online now   Reply With Quote
Old 02-24-2012, 05:38 AM   #5
Dulanic
Diamond Member
 
Dulanic's Avatar
 
Join Date: Oct 2000
Location: IL
Posts: 8,844
Default

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

Code:
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.
Dulanic is online now   Reply With Quote
Old 02-24-2012, 10:41 AM   #6
TheStu
Moderator
Mobile Devices & Gadgets
 
Join Date: Sep 2004
Location: New Cumberland, PA
Posts: 11,125
Default

Quote:
Originally Posted by Dulanic View Post
Forgot you wanted to verify the filename first... you could do something like this:

Code:
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.
__________________
Quote:
Originally Posted by mfenn View Post
The 6770M can play Crysis 2, for suitably small values of play
TheStu is online now   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 05:17 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.