Date Sensistive File Names
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Date Sensistive File Names

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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")

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I got it worked out. Thanks for the help.

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com