Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro to open files with varying names

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm looking for some code that will open a csv file on a daily basis that has the same prefix but the last part of the filename changes. The last part of the filename is the current date, in mmdd format e.g. 0322

    What I have tried so far has failed dismally, can anybody help?

    Workbooks.Open FileName:= _
    "C:testtest01 " & text(Date"mmdd") & ".csv"

    thanks

    Matt

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think the syntax you have is slightly wrong.
    I first tried the following...

    Workbooks.Open Filename:= _
    "C:testtest01 " & Month(Date) & Day(Date) & "csv"

    The problem with this, is that it treats March as 3 rather than 03 with the leading zeros. Therefore, if you have the following, inserting the leading zeros where necessary for months and days, it should work.

    If Month(Date) < 10 And Day(Date) < 10 Then

    Workbooks.Open Filename:= _
    "C:testtest01 0" & Month(Date) & "0" & Day(Date) & ".csv"

    ElseIf Month(Date) < 10 And Day(Date) > 9 Then

    Workbooks.Open Filename:= _
    "C:testtest01 0" & Month(Date) & Day(Date) & ".csv"

    ElseIf Month(Date) > 9 And Day(Date) < 10 Then

    Workbooks.Open Filename:= _
    "C:testtest01 " & Month(Date) & "0" & Day(Date) & ".csv"

    Else

    Workbooks.Open Filename:= _
    "C:testtest01 " & Month(Date) & Day(Date) & ".csv"

    End If

    I'm sure there's a better way though - this is very cumbersome!

    Rgds
    AJ


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ

    It may be cumbersome but it works, thanks for your help!

    cheers

    Matt

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try the following: -

    Workbooks.Open FileName:= _
    "C:testtest01 " & Format(Date, "mmdd")& ".csv"


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Much better!

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers

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
  •