Use date from calendar form in file name

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
709
Hi all,

I'm using this code to save a file using the active workbook's path but then adding a name (which it gets from a cell) and a date (that it gets from the calendar form). My problem is that the date comes out as 11/24 (which won't work as a file name) and I need it to be 11 24 2005. Any suggestions?
Thanks, Slink

Oops, here's the code so far:
Code:
Option Explicit
Public enddate As Date


Sub SaveAsCode()
    Dim path As String
    Dim wb As Workbook
    Dim name As String
    name = Range("g16").Value
    Set wb = ActiveWorkbook
    path = wb.path
    ActiveWorkbook.SaveAs Filename:= _
        path & "\" & name & enddate & ".xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Since this is a work in progress, I would like to highly recommend you think about having the filename in yyyymmdd format. This, when viewed in the Windows explorer, will "naturally" sort correctly in ascending (or descending) order by date.

You can use regular excel functions inside VBA code so consider using the TEXT() function along with the date function

This is a format I use frequently: =TEXT(TODAY(),"yyyymmdd")
You may wish to separate with dashes, unscores or spaces:
=TEXT(TODAY(),"yyyy-mmdd")
=TEXT(TODAY(),"yyyy-mm-dd")
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
709
OK, I thought I had it but still have problems. I put the enddate into a cell and then searched for / (which is character 47) and replaced it with a space. I also made sure that cell was formatted as text but it still turns back into the date format in the variable :confused: . Is there another format I have to use? Here's what I added to the calendar form code:

Code:
Dim ans As Integer
enddate = Calendar1.Value
ans = MsgBox("Is the end date you have chosen " & enddate & "?", vbYesNo, "End Date")
If ans = 7 Then End
Unload frmCalendar

Range("b3").Activate
Range("b3").Value = enddate
ActiveCell.Replace What:=Chr(47), Replacement:=" "
enddate = Range("b3").Value
Call SaveAsCode
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
709
Thanks nbrcrunch,
How would I write that into my code? Also, I don't want today's date, I want the date that was entered into the calendar and stored in the enddate variable. Thanks for the help! Slink
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Where are you getting the date from?

I don't see anywhere in the posted code where you are storing it in the variable enddate.

You should probably take a look at the Format function.
Code:
Msgbox Format(Date, "mm dd yyyy")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

nbrcrunch

There's no need to use worksheet functions.:)
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
709
Thanks for the reply Norie,
I want to use the variable enddate in the file name of the file saved. It's an expense report that is saved with the employee's name and the date that the employee selects from the calendar form. The variable enddate is assigned a value from the calendar in this code which is run when the user clicks on a button on the calendar:

Code:
Option Explicit


Private Sub CommandButton1_Click()

Dim ans As Integer 
enddate = Calendar1.Value 
ans = MsgBox("Is the end date you have chosen " & enddate & "?", vbYesNo, "End Date") 
If ans = 7 Then End 
Unload frmCalendar 

Call SaveAsCode 
end sub

Then I have more code in another module that uses the variables name and enddate to save the file with another name:

Code:
Option Explicit 
Public enddate As Date 


Sub SaveAsCode() 
    Dim path As String 
    Dim wb As Workbook 
    Dim name As String 
    name = Range("g16").Value 
    Set wb = ActiveWorkbook 
    path = wb.path 
    ActiveWorkbook.SaveAs Filename:= _ 
        path & "\" & name & enddate & ".xls" _ 
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 
    
End Sub

When I run this code though, it tries to insert "11/24" (If 11/24/2005 was chosen from the calendar) and that is not allowable in a file name. What I would like it to put in the file name is for example, "11 24 2005".

Thanks for any help, Slink
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Try this as I suggested.
Code:
ActiveWorkbook.SaveAs Filename:= _ 
        path & "\" & name & Format(Date, "mm dd yyyy")  & ".xls"
 
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,164,052
Messages
5,835,142
Members
430,343
Latest member
Sailingexcel

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