Help with saving a file based on cell values

speth

New Member
Joined
Feb 18, 2013
Messages
27
Hi,

I update a seating chart daily in my office, and I'm looking for help saving the file based off of a cell's value. I'm trying to have the file saved off of cell O28, which contains the date. I can't get the format to save in a yyyy-mm-dd format, though; it saves the file as "vacancy_tally_m/d/yyyy.xlsx" and I want it to save as "vacancy_tally_yyyy-mm-dd.xlsx". Here's what I've got:

Code:
Sub cmd_SaveToC_Drive()




Dim strPath As String
Dim strFolderPath As String
Dim fileSaveName As Variant
strFolderPath = "C:\"
ThisFile = "vacancy_tally_" & Range("O28").Value & ".xlsx"


fileSaveName = Application.GetSaveAsFilename(InitialFileName:=strFolderPath & ThisFile)
If fileSaveName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fileSaveName


End Sub

Any help? Thanks!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Change: ThisFile = "vacancy_tally_" & Range("O28").Value & ".xlsx"
to this: ThisFile = "vacancy_tally_" & Format(Range("O28"), "yyyy-mm-dd") & ".xlsx"
 
Upvote 0
Excellent, thanks for the help!

Just as another note - I changed the directory the file was saved to, but it kept adding the last folder location to the file name. I had to add "\" to the end of the directory location to get it to leave that out of the name of the file. Learn something every day :)
 
Upvote 0
Hello! I'm reviving this thread from the dead, apparently.

I've had an update to my work computer and version of Excel to Win 7, Excel 2010, and the code here doesn't work. Can someone help me get this working again? This is what I'm using:

Code:
Sub cmd_SaveToC_Drive()







Dim strPath As String
Dim strFolderPath As String
Dim fileSaveName As Variant
strFolderPath = "C:\Users\speth\Documents\Vacancy Workbook and Files\"
ThisFile = "vacancy_tally_" & Format(Range("AD9"), "yyyy-mm-dd") & ".xlsm"




fileSaveName = Application.GetSaveAsFilename(InitialFileName:=strFolderPath & ThisFile)
If fileSaveName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fileSaveName




End Sub

However, the save option window doesn't fill in the file name, it leaves the field blank and I have to type in the name myself (defeats the purpose of the macro).
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top