Excel vba - Open files with variable (dates) filenames

Question

I have the below code to open up files with variable file names, due to dates being in them. I personally save each file daily with the date stamp, ie this morning I saved a file with yesterday's date, 4.20.17.

This code will be run every Friday morning, and the goal is to load the last 5 work days' files (last Friday, this Monday, Tues, Wed, Thurs) grab some info out of those files (copy 2 cells from each), paste that info in a new sheet, and finally close each file.

Currently, the code is set to tell me when a file does not exist (for instance, last Friday was Good Friday, so Monday morning, I did not create any file for last Friday), and then ignore and move past that day.

The issue I currently have (besides the code being long and can probably be concatenated) is that a file exists for last Thursday, yet my code tells me there is none. I have been advised that this is because the code is actually looking at today (Thursday) and not a week ago Thursday, where there actually is a file.

Any assistance is appreciated. I removed a few days to make the below code less of a bear to look at, and a sample filename is "Agent Group Daily Summary 4.19.17"

Const strFilePath As String = "D:\Users\stefan.bagnato\Desktop\Daily Performance Summary\Agent Group Daily Summary "
Dim LastFridayDate, MondayDate, TuesdayDate, WednesdayDate, ThursdayDate As String
Dim fullFileNameLastFriday, fullFileNameMonday, fullFileNameTuesday, fullFileNameWednesday, fullFileNameThursday As String
Dim wbkLastFriday, wbkMonday, wbkTuesday, wbkWednesday, wbkThursdayOpen As Workbook

LastFridayDate = Format(Date - (Weekday(Date, vbFriday) - 1), "m.d.yy")
fullFileNameLastFriday = strFilePath & LastFridayDate & ".xls"
If Dir(fullFileNameLastFriday) = "" Then
    MsgBox "File for last Friday doesn't exist!"
    GoTo ExitLastFriday
End If
Set wbkLastFriday = Workbooks.Open(fullFileNameLastFriday, False, True)
Call BasicDailySummary
wbkLastFriday.Activate
Range("T2:T8").Copy
fp.Activate
Range("B3:B9").PasteSpecial xlPasteValues
wbkLastFriday.Activate
Range("F2:F8").Copy
fp.Activate
Range("G3:G9").PasteSpecial xlPasteValues
wbkLastFriday.Close SaveChanges:=False
ExitLastFriday:

MondayDate = Format(Date - (Weekday(Date, vbMonday) - 1), "m.d.yy")
fullFileNameMonday = strFilePath & MondayDate & ".xls"
If Dir(fullFileNameMonday) = "" Then
    MsgBox "File for Monday doesn't exist!"
    GoTo ExitMonday
End If
Set wbkMonday = Workbooks.Open(fullFileNameMonday, False, True)
Call BasicDailySummary
wbkMonday.Activate
Range("T2:T8").Copy
fp.Activate
Range("C3:C9").PasteSpecial xlPasteValues
wbkMonday.Activate
Range("F2:F8").Copy
fp.Activate
Range("H3:H9").PasteSpecial xlPasteValues
wbkMonday.Close SaveChanges:=False
ExitMonday:

....................................

ThursdayDate = Format(Date - (Weekday(Date, vbThursday) - 1), "m.d.yy")
fullFileNameThursday = strFilePath & ThursdayDate & ".xls"
If Dir(fullFileNameThursday) = "" Then
    MsgBox "File for Thursday doesn't exist!"
    GoTo ExitThursday
End If
Set wbkThursday = Workbooks.Open(fullFileNameThursday, False, True)
Call BasicDailySummary
wbkThursday.Activate
Range("T2:T8").Copy
fp.Activate
Range("F3:F9").PasteSpecial xlPasteValues
wbkThursday.Activate
Range("F2:F8").Copy
fp.Activate
Range("K3:K9").PasteSpecial xlPasteValues
wbkThursday.Close SaveChanges:=False
ExitThursday:

Community · Accepted Answer · 2018-05-08 21:49:49Z

That a file exists for last Thursday, yet my code tells me there is none

As I explained in the other question you asked yesterday, putting the vbMonday or vbThursday etc in the Format function doesn't magically tell VBA to return that day:

Hint: The vbFriday part of the Weekday function is not magically telling it to get friday's date. It's actually telling it that, for the sake of this function call, consider Friday to be the first day of the week. The Weekday function then returns an integer (the ordinal day of the week) which it subtracts from the Date.

So, you need to go back and understand how those functions work, you can't just dump constants in there willy-nilly without making an effort to understand what they're doing, or why. On that note, you absolutely need to read this and learn how to begin debugging and troubleshooting first. This describes basics of how to step through your code and examine variable's values/etc at runtime. These techniques are foundations you need to work with VBA.

Here is a list of statements available in VBA. This is documentation that explains things like "How to create a loop structure with For/Next, etc."

And you should go back through the dozen or so questions you've asked here, and mark accepted answers for those where an answer has solved your problem. This is just a basic point of etiquette: You've asked 11 questions here and only accepted 1 answer.

Note also that this sort of declaration does not do what you think it does:

Dim LastFridayDate, MondayDate, TuesdayDate, WednesdayDate, ThursdayDate As String
Dim fullFileNameLastFriday, fullFileNameMonday, fullFileNameTuesday, fullFileNameWednesday, fullFileNameThursday As String
Dim wbkLastFriday, wbkMonday, wbkTuesday, wbkWednesday, wbkThursdayOpen As Workbook

Only the last item in each of those statements are strongly typed, the rest are implicitly variant. You should strongly type all variables when possible, e.g.:

Dim wbkLastFriday As Workbook, wbkMonday As Workbook, wbkTuesday As Workbook, wbkWednesday As Workbook, wbkThursdayOpen As Workbook

And rather than using five different workbook objects (unless you really need 5 workbooks open at once, just use a single workbook object and operate within a loop, opening successive file at each iteration.

Dim wb as Workbook
Dim i as Long
For i = 1 to 5
    Set wb = Workbooks.Open(...)
    'Do something
    wb.Close()
Next

Getting to your actual problem:

A function like below will return an array of your date components. This returns the previous 7 days from the FirstDay (which defaults to Friday previous). You can use the Dir function as previously to simply test whether a filename is valid/existing (e.g., Sunday file doesn't exist, etc.), and skip over it if it's not valid.

Function GetFileNames(Optional FirstDay = vbFriday)
Dim filenames(1 To 7) As String
Dim i As Long
For i = 1 To 7
filenames(i) = Format(Date - (Weekday(Date, FirstDay) + i), "m.d.yy")
Next
GetFileNames = filenames
End Function

Thanks David. I am still fairly new to the site, so I did not understand the check mark. I am aware of how to debug; the issue is not that I did not know how to debug, rather that I did not understand what the issue was and why it was happening. VBA is still fairly foreign to me. Anything I can do to make the code run faster and not be as long I would love to do, but in the end, it will only be used by me and just needs to get the job done. I'm using this as learning as well.
upvote for the "basic point of etiquette". Naah, just kidding :P, respects for the instructive and detailed answer.

A.S.H · Accepted Answer · 2017-04-20 13:44:28Z

1

It seems that you want your search to start from yesterday instead of today. If so, you can try changing

ThursdayDate = Format(Date - (Weekday(Date, vbThursday) - 1), "m.d.yy")

into

ThursdayDate = Format(Date - (Weekday(Date - 1, vbThursday)), "m.d.yy")

and generalize it to other week days. In fact what it does now is that when it runs, say, on this Thursday, it looks up for the file of last Thursday...

answered Apr 20, 2017 at 13:44

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Collectives™ on Stack Overflow

Excel vba - Open files with variable (dates) filenames

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related