Solutions Through Learning
Callback
Sign in
Basket
Call us on 0207 987 3777 or Search our website
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Dynamic file opening
Resolved · Medium Priority · Version 2003
Geoff has attended:
Excel VBA Intro Intermediate course
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?
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
Great. Thank you. - Final question, is their way to stipulate business date -1?
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.
|
|
Excel tip:Shortcut for deleting all comments in a spreadsheetIf you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by: |
TrustScore
4.8
/ 5
2236 reviews
Connect with us:
We will call you back
Call for assistance
Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE
2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom
We'll call during UK business hours
Server loaded in 0.08 secs.
