Open workbooks - filenames change daily

[Guest]

I have a user who needs to open five workbooks every morning. These workbooks
are named with yesterday's date and then an alpha identifier (e.g. 111605SL).
This means that the filename changes daily and not always to the previous
date (e.g. today he opens yesterday's, tomorrow he opens today's, on Monday
he will open Friday's).

Is it even possible to write a macro to open these workbooks?

Your help is appreciated.

 

[Bernie Deitrick]

Gillian,

Try the macro below. Change the path to reflect the folder path where the files are stored, and
change the alpha codes for each of the five files.

Note that this won't automatically account for holidays like it accounts for weekends.

HTH,
Bernie
MS Excel MVP

Sub OpenFilesByDateCode()
Dim myDateCode As String
Dim myPath As String

myPath = "C:\Folder1Name\Folder2Name\"

myDateCode = Format(Date - IIf(Date Mod 7 = 2, 3, 1), "mmddyy")

If MsgBox("Open files from DateCode " & _
myDateCode & "?", vbYesNo) = vbYes Then
Workbooks.Open myPath & myDateCode & "CL.xls"
Workbooks.Open myPath & myDateCode & "AB.xls"
Workbooks.Open myPath & myDateCode & "CD.xls"
Workbooks.Open myPath & myDateCode & "EF.xls"
Workbooks.Open myPath & myDateCode & "GH.xls"
End If
End Sub

 

[Guest]

Thanks Bernie I'll give it a try!
--
Thanks,
GillianHG

Gillian,

Try the macro below. Change the path to reflect the folder path where the files are stored, and
change the alpha codes for each of the five files.

Note that this won't automatically account for holidays like it accounts for weekends.

HTH,
Bernie
MS Excel MVP

Sub OpenFilesByDateCode()
Dim myDateCode As String
Dim myPath As String

myPath = "C:\Folder1Name\Folder2Name\"

myDateCode = Format(Date - IIf(Date Mod 7 = 2, 3, 1), "mmddyy")

If MsgBox("Open files from DateCode " & _
myDateCode & "?", vbYesNo) = vbYes Then
Workbooks.Open myPath & myDateCode & "CL.xls"
Workbooks.Open myPath & myDateCode & "AB.xls"
Workbooks.Open myPath & myDateCode & "CD.xls"
Workbooks.Open myPath & myDateCode & "EF.xls"
Workbooks.Open myPath & myDateCode & "GH.xls"
End If
End Sub