Help with saving a file based on cell values

speth

New Member
Joined
Feb 18, 2013
Messages
22
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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

certifydgangsta

New Member
Joined
Jan 29, 2014
Messages
38
Change: ThisFile = "vacancy_tally_" & Range("O28").Value & ".xlsx"
to this: ThisFile = "vacancy_tally_" & Format(Range("O28"), "yyyy-mm-dd") & ".xlsx"
 

speth

New Member
Joined
Feb 18, 2013
Messages
22
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 :)
 

speth

New Member
Joined
Feb 18, 2013
Messages
22
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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
Top