File Path Variable

[Braunschweiger]

I have a workbook that processes data and puts it into a usable format for people in my department. The workbook pulls data from about 12 other workbooks. Currently, my script points to a location on my hard drive. I'd like the file location to be a variable so...I can hand this workbook off to other people so...they can run the report themselves. I'd like them to be able to input the file location on their PC...into the script. So, file location is a variable and could change but...the file names of the 12 feeder files will not change. Below is an example of what I'm currently using:

Dim wkb As Workbook
Dim sht As Worksheet
Set wkb = Workbooks.Open("C:\Users\David\Documents\HUD WORK\Portfolio Snapshot\Dumps\dbo_active_financing.xlsx")
Set sht = wkb.Sheets("dbo_active_financing")
sht.Activate


Dim wkb As Workbook
Dim sht As Worksheet
Set wkb = Workbooks.Open("C:\Users\David\Documents\HUD WORK\Portfolio Snapshot\Dumps\dbo_active_financing_participant.xlsx")
Set sht = wkb.Sheets("dbo_active_financing_participan")
sht.Activate

The first script points to a location on my PC and opens a file named "dbo_active_financing". The second script does the same thing except...opens "dbo_active_financing_participan" rather than..."dbo_active_financing". I want the user to be able to change the bold, blue part of the script below.

C:\Users\David\Documents\HUD WORK\Portfolio Snapshot\Dumps\dbo_active_financing.xlsx
C:\Users\David\Documents\HUD WORK\Portfolio Snapshot\Dumps\dbo_active_financing_participant.xlsx"

As always...very grateful for every ones help!

 

[Fluff]

How about

Dim wkb As Workbook
Dim sht As Worksheet
Dim Pth As String
Pth = "C:\Users\David\Documents\HUD WORK\Portfolio Snapshot\Dumps\"
Set wkb = Workbooks.Open(Pth & "dbo_active_financing.xlsx")
Set sht = wkb.Sheets("dbo_active_financing")
sht.Activate

Or you could use

Pth = Environ("userprofile") & "\Documents\HUD WORK\Portfolio Snapshot\Dumps\"