Open workbook with dynamic filename

[JDW]

I'm attempting to have part of my script open an excel file has a dynamic name that changes every week. The current name of the file is app_opdiv_piv_12_30_2016.xlsx. I've tried a number of different ways to open this file but keep getting the same error: "Runtime error 1004"

This is one version of the code that I've tried: Workbooks.Open fileName:="C:\SCMS Reports\app_opdiv_piv_*" & ".xlsx"

If I put the entire filename in the path it opens successfully. Can we not use wildcards to open files? Please excuse my questions I'm new to VBA, any help would be much appreciated, thanks!

[offthelip]

try this, I presume the file name changes every Friday since the date you have shown is a Friday, if it isn't you just need to adjust number which is subtracted from todays date.

Note I haven't test the opening of the file of that name, but the Msgbox looks Ok to me.

weekd = Weekday(Date)
lastfriday = Day(Date - weekd - 1)
monthtxt = Month(Date - weekd - 1)
yeartxt = Year(Date - weekd - 1)
pathtxt = "C:\SCMS Reports\app_opdiv_piv_" & monthtxt & "_" & lastfriday & "_" & yeartxt & ".xlsx"
MsgBox (pathtxt)
Workbooks.Open Filename:=pathtxt

[Bob Phillips]

try this, I presume the file name changes every Friday since the date you have shown is a Friday,

Your code will get the previous Friday on a Friday date, which cannot be what is wanted, and it can be done in a single step


lastfriday = Date + 1 - Weekday(Date + 2)
pathtxt = "C:\SCMS Reports\app_opdiv_piv_" & Format(lastfriday, "mm_dd_yyyy") & ".xlsx"

[JDW]

try this, I presume the file name changes every Friday since the date you have shown is a Friday, if it isn't you just need to adjust number which is subtracted from todays date.

Note I haven't test the opening of the file of that name, but the Msgbox looks Ok to me.

weekd = Weekday(Date)
lastfriday = Day(Date - weekd - 1)
monthtxt = Month(Date - weekd - 1)
yeartxt = Year(Date - weekd - 1)
pathtxt = "C:\SCMS Reports\app_opdiv_piv_" & monthtxt & "_" & lastfriday & "_" & yeartxt & ".xlsx"
MsgBox (pathtxt)
Workbooks.Open Filename:=pathtxt




Thank you, this is exactly what I was looking for and it's working perfectly!

[JDW]

Your code will get the previous Friday on a Friday date, which cannot be what is wanted, and it can be done in a single step


lastfriday = Date + 1 - Weekday(Date + 2)
pathtxt = "C:\SCMS Reports\app_opdiv_piv_" & Format(lastfriday, "mm_dd_yyyy") & ".xlsx"


Very cool, I will have to try this as well!

So this app_opdiv report comes in on Friday but doesn't actually get processed until a few days later, usually on Sunday evenings. Will this open the file even if it's after the Friday's date?

[Paul_Hossler]

I'd do it slightly differently




Option Explicit

Sub FridayReport()
Dim D As Long, M As Long, Y As Long, DOW As Long
Dim PathTxt As String
Dim DT As Date

DOW = Weekday(Date)
D = Day(Date)
M = Month(Date)
Y = Year(Date)

If DOW <> vbFriday Then
DT = DateSerial(Y, M, D - DOW - 1)
Else
DT = Date
End If

PathTxt = "C:\SCMS Reports\app_opdiv_piv_" & Format(DT, "mm_dd_yyyy") & ".xlsx"

MsgBox PathTxt
End Sub






If I run it today (5 Jan 2017) it generates 30 Dec 2016 (the previous Friday)

If I run it tomorrow (6 Jan 2017) it generates 6 Jan 2017 (which is a Friday)

[Bob Phillips]

I'd do it slightly differently

If I run it today (5 Jan 2017) it generates 30 Dec 2016 (the previous Friday)

If I run it tomorrow (6 Jan 2017) it generates 6 Jan 2017 (which is a Friday)

Isn't that exactly what I did?

[Paul_Hossler]

Isn't that exactly what I did?

Yes, same basic algorithm.

But I was wordy-er (more wordy?)

I wanted to emphasis the 'Friday' aspect so I explicitly had the vbFriday test in there

[Bob Phillips]

I wanted to emphasis the 'Friday' aspect so I explicitly had the vbFriday test in there

Ah, I see your reasoning.

You could also get that emphasis with my code, modified a bit


Dim dateasof As Date

dateasof = Date + 1 - Weekday(Date + (8 - vbFriday) Mod 7)
Debug.Print "Date of last friday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbThursday) Mod 7)
Debug.Print "Date of last thursday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbSaturday) Mod 7)
Debug.Print "Date of last saturday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbSunday) Mod 7)
Debug.Print "Date of last sunday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbMonday) Mod 7)
Debug.Print "Date of last monday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbTuesday) Mod 7)
Debug.Print "Date of last tuesday " & dateasof
dateasof = Date + 1 - Weekday(Date + (8 - vbWednesday) Mo