PDA

View Full Version : Solved: Assigning a date variable for use in a file extension



Poundland
06-26-2008, 04:22 AM
Dear all,

I have this problem.


I am trying to code a date variable that can be used in a file extension and can be calculated to cahnge the date.

I have tried this code;

Dim strDate As String
Dim strNewDate As String
strDate = Format(Now, "dd mmm yyyy")

This give me the date and the date format that I require for my file extension. But when I try to perform the following calculation is does not work.

strDate = Format((strNewDate - 1), "dd mmm yyyy")

It will work when I assign the variables as this;

Dim strDate As Date
Dim strNewDate As Date

But this then does not allow me to use the date in a file extension as I cannot change the format of this date from __/___/____ and / cannot be used in a file extension.

Have you got any ideas.

Basically I am using the two date variables as I want to create the file extension with a Monday date regardless of which day you run the code on, and then want to open another file which is 7 days prior to the Monday date.

Cosmo
06-26-2008, 05:50 AM
Build your fileExtension string then use

Replace(fileExtension ,"/","_")

Simon Lloyd
06-26-2008, 05:54 AM
The following code will save as a monday date of that week Mon-Sun
Sub datez()
Dim MyDate As Date
Select Case Format(Date, "ddd")
Case Is = "Tue"
MyDate = Date - 1
Case Is = "Wed"
MyDate = Date - 2
Case Is = "Thur"
MyDate = Date - 3
Case Is = "Fri"
MyDate = Date - 4
Case Is = "Sat"
MyDate = Date - 5
Case Is = "Sun"
MyDate = Date - 6
Case Is = "Mon"
MyDate = Date
End Select
ThisWorkbook.SaveAs (ThisWorkbook.Name & " " & Format(MyDate, "dd-mm-yyyy"))
End Sub

Cosmo
06-26-2008, 06:27 AM
The following code will save as a monday date of that week Mon-Sun
Sub datez()
Dim MyDate As Date
Select Case Format(Date, "ddd")
Case Is = "Tue"
MyDate = Date - 1
Case Is = "Wed"
MyDate = Date - 2
Case Is = "Thur"
MyDate = Date - 3
Case Is = "Fri"
MyDate = Date - 4
Case Is = "Sat"
MyDate = Date - 5
Case Is = "Sun"
MyDate = Date - 6
Case Is = "Mon"
MyDate = Date
End Select
ThisWorkbook.SaveAs (ThisWorkbook.Name & " " & Format(MyDate, "dd-mm-yyyy"))
End Sub
Simplified without the 'Case' statement:

Dim MyDate As Date
MyDate = Date - Weekday(Date - 1, 1) + 1
ThisWorkbook.SaveAs (ThisWorkbook.Name & " " & Format(MyDate, "dd-mm-yyyy"))

Simon Lloyd
06-26-2008, 06:29 AM
Cosmo nice! but the reason for all the Case statements was to allow the Op to work out what was occurring to cause the date to always be Monday's date.

Cosmo
06-26-2008, 06:40 AM
Cosmo nice! but the reason for all the Case statements was to allow the Op to work out what was occurring to cause the date to always be Monday's date.
Understandable. I do like concise code, but I know that sometimes the longer, easier to read code is preferred.

Especially since it probably took me longer to remember how to write my one line of code than it did for you to type out your entire solution. ;)

marshybid
06-26-2008, 06:53 AM
Hi All,

Would it not be advisable to add filepath info to the code



Dim FPath As String
Dim MyDate As Date
FPath = "C:\My Documents\"
MyDate = Date - Weekday(Date - 1, 1) + 1
ThisWorkbook.SaveAs FileName:=FPath & " " & format(MyDate, "dd-mm-yyyy") & ".xls"


Used C:\MyDocuments purely as an example

Marshybid

mdmackillop
06-26-2008, 07:47 AM
If you'll need to sort files where the date is used to differentiate versions, consider using "yy mm dd" format

Simon Lloyd
06-26-2008, 07:57 AM
If you'll need to sort files where the date is used to differentiate versions, consider using "yy mm dd" formatIt's always the simple advice thats best! thats a very good point Malcolm.