• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

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
0
Justincut
Asked:
Justincut
  • 4
1 Solution
 
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:
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:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now