dynamic file opening
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Dynamic file opening

Dynamic file opening

resolvedResolved · Medium Priority · Version 2003

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

Fri 1 Oct 2010: Automatically marked as resolved.


 

Excel articles

Please browse our web site to find out more about
microsoft excel 2003 vba courses and other training courses.

Excel tip:

Shortcut for deleting all comments in a spreadsheet

If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:

1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.

2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.

3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

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

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.