File Path Outside of Subroutine

Braunschweiger

Board Regular
Joined
Feb 19, 2014
Messages
104
Hello,
I need help figuring out how to place a variable file path in it's own location so that, other subroutines can go to that file path in order to open different excel workbooks stored in the folder the variable file path points to. This will help me be more efficient. If the file path changes (e.g., I place the folder that contains my excel workbooks in a different location), I won't have to go through my entire code and change the file path in every subroutine.

The code below works. It follows the file path "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" and opens workbook "dbo_active_financing.xlsx"
My problem is...the entire code is contained in 1 sub.
VBA Code:
Public Sub openfile_THIS_WORKS_v2_USE_THIS_ONE()

Dim strFName As String 'One Sub
Dim myVar As String 'Many Subs
Dim wkb As Workbook 'One Sub
Dim sht As Worksheet 'One Sub

myVar = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" 'Many Subs
strFName = myVar & "dbo_active_financing.xlsx" 'One Sub

Set wkb = Workbooks.Open(strFName) 'One Sub
Set sht = wkb.Sheets("dbo_active_financing") 'One Sub
sht.Activate 'One Sub

End Sub

What I would like to do is, place the below code in it's own location so that other subroutines can use it.
Code:
Dim myVar As String 'Many Subs
myVar = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" 'Many Subs

So, the code below (which is actually the first code presented without the second code) could acquire the file path (along with any other subroutine) "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" which is contained in it's own location.
VBA Code:
Public Sub openfile_THIS_WORKS_v2_USE_THIS_ONE()

Dim strFName As String 'One Sub
Dim wkb As Workbook 'One Sub
Dim sht As Worksheet 'One Sub

strFName = myVar & "dbo_active_financing.xlsx" 'One Sub

Set wkb = Workbooks.Open(strFName) 'One Sub
Set sht = wkb.Sheets("dbo_active_financing") 'One Sub
sht.Activate 'One Sub

End Sub

Thanks in advance!
Dave
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Just move "Dim myVar As String" outside of a sub in the main module
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,439
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In a normal module, use:

Code:
Public Const myVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" 'Many Subs
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Put this at the very top of a standard module, before any code
VBA Code:
Global Const MyVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\"
Beaten 2it :(
 

Braunschweiger

Board Regular
Joined
Feb 19, 2014
Messages
104

ADVERTISEMENT

Just move "Dim myVar As String" outside of a sub in the main module
Unfortunately, this didn't work but. I did find a solution though. Thanks a ton for taking a look at the issue!
 

Braunschweiger

Board Regular
Joined
Feb 19, 2014
Messages
104

ADVERTISEMENT

Put this at the very top of a standard module, before any code
VBA Code:
Global Const MyVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\"
Beaten 2it :(
This worked as well! Thanks a Ton.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Braunschweiger

Board Regular
Joined
Feb 19, 2014
Messages
104
Question:

Module specific?
VBA Code:
Public Const myVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" 'Many Subs

Any module can use it?
Code:
Global Const MyVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\"
 

Braunschweiger

Board Regular
Joined
Feb 19, 2014
Messages
104
The full working script.

Code:
'EITHER OF THE LINES OF CODE BELOW (AT THE TOP HERE) WILL PERMIT ALL SUBS TO USE IT (MyVar).

'I believe "Global" permits other modules to use "MyVar".
'I believe "Public" is module specific.

'Global Const MyVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\"
Public Const MyVar As String = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\"

'There should be a line here ---------------------------------------------------------------------------------------------------------------

Public Sub openfile_THIS_WORKS_v2_USE_THIS_ONE()

Dim strFName As String 'One Sub
'Dim myVar As String 'Many Subs
Dim wkb As Workbook 'One Sub
Dim sht As Worksheet 'One Sub

'myVar = "C:\Users\david\Documents\a.Portfolio Snapshot Template\Dumps\" 'Many Subs
strFName = MyVar & "dbo_active_financing.xlsx" 'One Sub

Set wkb = Workbooks.Open(strFName) 'One Sub
Set sht = wkb.Sheets("dbo_active_financing") 'One Sub
sht.Activate 'One Sub
Range("A1").Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,450
Members
415,970
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top