Power Query Course in Spanish
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


Some videos you may like

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
  •