Opening an Excel spreadsheet with a Macro where the path is within an Excel Cell

Hi Guys, I have an Excel macro which opens a spreadsheet path called : "g:\Finance\Price\Jan\Summary300115.xlsm" . It is variable path as the month folder changes (eg. Jan -> Feb). I want to feature the path on a Procedure tab from the source spreadsheet within cell "A10" and want to run the Macro so it open whatever path is in this Cell so all the members of my team who do not know about Macros can run knowing which spreadsheet they are opening. How do I do this? Cheers Justin
JustincutAsked:
Who is Participating?
 
Wilder1626Connect With a Mentor Commented:
What you can also do is for example

Put in A10: g:\Finance\Price
Put in B10: Summary300115.xlsm

Then run the below macro
Workbooks.Open Filename:=Range("A10") & "\" & Format(Now, "mmm") & "\" & Range("B10")

Open in new window

0
 
Wilder1626Commented:
Hi
Is this what you are looking for?
If your full path is in cell A10, the below macro will open it.

Put this in A10: g:\Finance\Price\Jan\Summary300115.xlsm
Then run this macro
Workbooks.Open Filename:=Range("A10")

Open in new window

0
 
JustincutAuthor Commented:
Yep. Is there a way I can link the path to change when the Month changes eg. Jan -> Feb without me having to physically type it?
0
 
Wilder1626Commented:
This can also tell you if the folder does not exist:

On Error GoTo HANDLER
Workbooks.Open Filename:=Range("A1") & "\" & Format(Now, "mmm") & "\" & Range("A2")
HANDLER:
MsgBox "Folder does not exist"

Open in new window

0
 
Wilder1626Commented:
You can also do the below. It will put in the message the path it's looking for:
On Error GoTo HANDLER
Workbooks.Open Filename:=Range("A10") & "\" & Format(Now, "mmm") & "\" & Range("B210")
Exit Sub
HANDLER:
MsgBox Range("A10") & "\" & Format(Now, "mmm") & "\" & Range("B10") & " Folder does not exist"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.