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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,880
Messages
5,834,222
Members
430,265
Latest member
CDane

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