Solved

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

Posted on 2015-02-22
5
100 Views
Last Modified: 2015-02-22
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
Comment
Question by:Justincut
  • 4
5 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624140
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
 

Author Comment

by:Justincut
ID: 40624145
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
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 40624153
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624156
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624162
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question