View Full Version : [SOLVED:] macro to open workbook in shifting folders
jmutsche
11-14-2013, 06:21 AM
What I am looking for is a way for this macro to open a workbook that we create monthly. for example next month I would want it to automatically update to December. does anyone know if there is a way to do that? is that done with the update links and a variable or what? please help...
Sub Macro1()
'
' Macro1 Macro
'
'
Workbooks.Open Filename:= _
"S:\Supervisor\Productivity\2013\November\November 2013 Productivity Tracking Results_V2.0.xlsm" _
, UpdateLinks:=0
End Sub
p45cal
11-14-2013, 02:04 PM
perhaps something along the lines of:
Sub Macro1()
fname = "S:\Supervisor\Productivity\" & Year(Date) & "\" & Format(Date, "mmmm") & "\" & Format(Date, "mmmm") & " Productivity Tracking Results_V2.0.xlsm"
'Debug.Print fname 'uncomment this line and check the flename in the immediate pane.
Workbooks.Open Filename:=fname, UpdateLinks:=0
End Sub?
Realise though, that this uses the computer's system date/time, so after midnight November 30th goes past, the code will try to open December's file when it's run.
jmutsche
11-14-2013, 02:39 PM
You are a superhero!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! it wasn't quite there but from what you gave me I was able to get it. This seriously opened up a whole new world for me. I am so grateful. If I were anywhere near you right now I would slap you on the Ass and acknowledge the **** out of that play!!!
seriously thank you so much
James Mutscheller
p45cal
11-14-2013, 02:45 PM
oops:
fname = "S:\Supervisor\Productivity\" & Year(Date) & "\" & Format(Date, "mmmm") & "\" & Format(Date, "mmmm yyyy") & " Productivity Tracking Results_V2.0.xlsm"
or:
fname = "S:\Supervisor\Productivity\" & Format(Date, "yyyy\\mmmm\\mmmm yyyy ") & " Productivity Tracking Results_V2.0.xlsm"
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.