Geoff has attended:
Excel VBA Intro Intermediate course
Dynamic file opening
I need to refernce another excel workbook whose name changes on a daily basis based on todays date. The folder changes dependent on business day - 1 date. I.e all other path is the same.
C:/location/MTH/DAY-1.xls
Can I write a procedure that recognises that the file and folder to open is todays date and month (folder) -1 business day?
RE: Dynamic file opening
Hi Geoff, thanks for your query. Most people write a bespoke function for this, but you might be able to get away with using some Date functions and concatenation. The following code will drop "C:/location/9/23.xls" into a variable called mynewfilename which you can then cite elsewhere in your code. It will dynamically update using the computer clock.
-----------
Dim mynewfilename As String
mynewfilename = "C:/location/" & Month(Date - 1) & "/" & Day(Date - 1) & ".xls"
MsgBox mynewfilename
------------
Here's a useful list of Date orientated VBA functions which you may need to modify my code:
http://www.likeoffice.com/28057/excel-date
Hope this helps,
Anthony
RE: Dynamic file opening
Great. Thank you. - Final question, is their way to stipulate business date -1?
RE: Dynamic file opening
You could modify as below, putting the date of your choice into a variable and citing that in your code. Note where I have put "mydate - 1" I am subtracting a day from the day as requested
-----
Dim mynewfilename As String
Dim mydate As Date
mydate = #9/6/1973#
mynewfilename = "C:/location/" & Month(mydate - 1) & "/" & Day(mydate - 1) & ".xls"
MsgBox mynewfilename
-----
Hope this helps,
Anthony
