Use date from calendar form in file name

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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
701
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
701
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,056
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,056
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
701
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,056
Office Version
  1. 365
Platform
  1. Windows
Try this as I suggested.
Code:
ActiveWorkbook.SaveAs Filename:= _ 
        path & "\" & name & Format(Date, "mm dd yyyy")  & ".xls"
 

Watch MrExcel Video

Forum statistics

Threads
1,118,318
Messages
5,571,527
Members
412,401
Latest member
allenayres83
Top