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.
What I would like to do is, place the below code in it's own location so that other subroutines can use it.
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.
Thanks in advance!
Dave
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