Macro to open files with varying names

[Matt]

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

 

[AJ]

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

 

[Matt]

AJ

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

cheers

Matt

 

[Mudface]

You could try the following: -

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

 

[AJ]

Much better!

 

[Matt]

Cheers