Importing file with date that changes daily

[jthompson011]

Using VBA in excel 2010, I'm trying to code the macro so that it automatically takes into consideration the filename with the date changing daily.

Workbooks.Open Filename:= _
"S:\******\Holdings As Of 10-05-2016.xlsx"
Columns("A:Z").Select
Selection.Copy

The date will be changing daily to where it will be prior days date in the *.xlsx file. Any advice for a beginner?

Thanks in advance.

[JKwan]

"S:\******\Holdings As Of " & Format(Now - 1, "mm-dd-yyyy") & ".xlsx"

[jthompson011]

Thank you!

[jthompson011]

"S:\******\Holdings As Of " & Format(Now - 1, "mm-dd-yyyy") & ".xlsx"


Would this work when attached to a business day/holiday schedule? For instance, on mondays I would need it to be ".....& Format(Now - 3, "mm....."

Thanks again!

[JKwan]

yes, you can do that, however, I would make it better by:

Check if it is Monday, if it is Monday, then subtract 3 else subtract 1


Sub test()
Select Case Weekday(Now)
Case Is = vbMonday
Workbooks.Open Filename:= _
"S:\******\Holdings As Of " & Format(Now - 3, "mm-dd-yyyy") & ".xlsx"
Case Else ' any other time do this
Workbooks.Open Filename:= _
"S:\******\Holdings As Of " & Format(Now - 1, "mm-dd-yyyy") & ".xlsx"

End Select

Columns("A:Z").Select
Selection.Copy
End Sub

[jthompson011]

yes, you can do that, however, I would make it better by:

Check if it is Monday, if it is Monday, then subtract 3 else subtract 1


Sub test()
Select Case Weekday(Now)
Case Is = vbMonday
Workbooks.Open Filename:= _
"S:\******\Holdings As Of " & Format(Now - 3, "mm-dd-yyyy") & ".xlsx"
Case Else ' any other time do this
Workbooks.Open Filename:= _
"S:\******\Holdings As Of " & Format(Now - 1, "mm-dd-yyyy") & ".xlsx"

End Select

Columns("A:Z").Select
Selection.Copy
End Sub




This is excellent. One more question. Would there be a way to add any holidays here as well? I've used a formula in the past where I could reference a second tab and put date on the tab as 'holidays' where I would want to skip. I'd like to do the same thing here, however i'm not sure how to go about it. Here is the formula i've used in the past.

=WORKDAY(TODAY(),1,Holidays!B1:B9),"YYYYMMDD"

Thanks!

[JKwan]

I will take a look tomorrow, it is Thanksgiving in Canada. Cooking up a storm

[JKwan]

give this a go:

since yesterday was Turkey Day
A1 contains Turkey Day - 10-07-2016
A2 contains Remembrance Day - 11-10-2016


Sub test2()
Dim sDate As Date

sDate = Application.WorkDay(Now, -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox Format(sDate, "mm-dd-yyyy")
sDate = Application.WorkDay("11/14/2016", -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox Format(sDate, "mm-dd-yyyy")
End Sub

[jthompson011]

give this a go:

since yesterday was Turkey Day
A1 contains Turkey Day - 10-07-2016
A2 contains Remembrance Day - 11-10-2016


Sub test2()
Dim sDate As Date

sDate = Application.WorkDay(Now, -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox Format(sDate, "mm-dd-yyyy")
sDate = Application.WorkDay("11/14/2016", -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox Format(sDate, "mm-dd-yyyy")
End Sub



Sorry to bother you but i'm having a little trouble getting the holiday code to work. Essentially i'm trying to get it to pull a prior day file (see the now-1; now-3 file you helped with previously). How would i go about coding the holiday schedule into that string so that the macro would know to skip over not only weekends but also the holidays? I'm not having much luck with the previous.

Thanks again for all your help. I feel like I should send you a case of beer or something.

[JKwan]

so, if you look at my example code. Range A1 and A2 contains the holidays (just add as many as you like, just making sure that your range is reflected). sDate will contain your "date" which you can merge with the previous code. If you run the code, you will see what I mean.

updated post with code.
First example is what you need, second example is illustration only.....


Sub test2()
Dim sDate As Date

sDate = Application.WorkDay(Now, -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox "S:\******\Holdings As Of " & Format(sDate, "mm-dd-yyyy") & ".xlsx"

sDate = Application.WorkDay("11/14/2016", -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox "S:\******\Holdings As Of " & Format(sDate, "mm-dd-yyyy") & ".xlsx"
End Sub

[jthompson011]

so, if you look at my example code. Range A1 and A2 contains the holidays (just add as many as you like, just making sure that your range is reflected). sDate will contain your "date" which you can merge with the previous code. If you run the code, you will see what I mean.

updated post with code.
First example is what you need, second example is illustration only.....


Sub test2()
Dim sDate As Date

sDate = Application.WorkDay(Now, -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox "S:\******\Holdings As Of " & Format(sDate, "mm-dd-yyyy") & ".xlsx"

sDate = Application.WorkDay("11/14/2016", -1, Worksheets("Sheet1").Range("A1:A2"))
MsgBox "S:\******\Holdings As Of " & Format(sDate, "mm-dd-yyyy") & ".xlsx"
End Sub


That worked perfectly, thank you again for all your help. You definitely saved me from banging my head through a wall!