Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Dynamic file opening
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Dynamic file opening
Resolved · Medium Priority · Version 2003
Reply Wed 22 Sep 2010, 17:02Delegate Geoff said...
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?
For upcoming training course dates see: Pricing & availability
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
Reply Fri 24 Sep 2010, 12:02Delegate Geoff said...
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
Fri 1 Oct 2010: Automatically marked as resolved.
|