Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbooks - filenames change daily
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. -- Thanks, GillianHG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbooks - filenames change daily
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 "GillianHG" wrote in message ... 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. -- Thanks, GillianHG |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open workbooks - filenames change daily
Thanks Bernie I'll give it a try!
-- Thanks, GillianHG "Bernie Deitrick" wrote: 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 "GillianHG" wrote in message ... 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. -- Thanks, GillianHG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking to Workbooks that Change Name Daily | Excel Worksheet Functions | |||
Linking to Workbooks that Change Daily | Excel Discussion (Misc queries) | |||
How to link cells in two workbooks that change daily? | Excel Discussion (Misc queries) | |||
XL97 changes filenames upon open | Excel Discussion (Misc queries) | |||
Macro to Open all workbooks in a folder and change default font | Excel Programming |