Date Sensistive File Names

[jrnyman]

I need a macro to open a file daily whose name depends on the date. The filename is formatted as "Filename DDMM" where "ThisFile 0103" would refer to the file for March 1st. Any suggesstion for how I can use this dynamic filename in a macro? For now I have the user enter the four digit code in a cell and pass a concatenated reference as the filename. I'd like something more automatic. Thanks.

 

[Dave Hawley]

Hi

Not to sure what you call your file but this code will parse the current date in the format you want to a string.

Dim strWbk As String
strWbk = "This File " & Format(Date, "ddmm")

 

[jrnyman]

The filename is "Transactions DDMM", and I need to open it from within a shared folder. How can I combine the date string with the filename and the file's location for opening it? Thanks.

 

[Dave Hawley]

Try

Sub OpenIt()
Dim strWbk As String
strWbk = "Transactions " & Format(Date, "ddmm") & ".xls"
Workbooks.Open Filename:="C:OzGrid" & strWbk
End Sub

Just change the path to suit.

 

[jrnyman]

that worked perfectly. Can you help me with the syntax for activating the window named "Transactions DDMM.xls" where Transactions DDMM is the variable strWbk? I don't know how to start with the variable and end with text (your example has the opposite). Thanks.

 

[jrnyman]

I got it worked out. Thanks for the help.

 

[Dave Hawley]

Certainly, no problem!Point to note here:

1. The Variable has now been declared at the Module level. This way it will retain it's value.


Dim strWbk As String

Sub OpenIt()
strWbk = "Transactions " & Format(Date, "ddmm") & ".xls"
Workbooks.Open Filename:="C:OzGrid" & strWbk
End Sub

Sub ActivateIt()
Windows(strWbk).Activate
End Sub