VBA to Find Path that Changes Based on User

seguin85

Active Member
Joined
Mar 17, 2015
Messages
278
Office Version
  1. 365
Platform
  1. Windows
I have a code that I am building that will open a workbook in a different location based on what user is using the code.

The path where the code will be running from will be something like Project Management\Employee Name\Projects\Project Number\subfolder 1\sub folder 2.

And I will need the code to look into this path Project Management\Employee Name\Projects\Excelfile.xslm.

Is there a way I can do this? I know I can use Application.ActiveWorkbook.Path to find the original path, but how can I go back a couple of folder levels??

Thanks in advance.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Employee Name is the real name of the folder? If not, you might be needing to replace that with Environ("username") property.
You could also use Left function to grab left 42 characters if it's going to be consistent. Hard to give more focused answers as the details are a bit vague to me.
 
Upvote 0
You can use the standard DOS/Windows parent folder "..\" syntax to go up 3 folder levels:
VBA Code:
    Dim otherWb As Workbook
    Set otherWb = Workbooks.Open(ThisWorkbook.Path & "\..\..\..\Excelfile.xlsm")
 
Upvote 0
Employee Name is the real name of the folder? If not, you might be needing to replace that with Environ("username") property.
You could also use Left function to grab left 42 characters if it's going to be consistent. Hard to give more focused answers as the details are a bit vague to me.

The name of the folder is the actual employee's name.

The file name where the excel file is located is C:\\Project Management\Bob Jones\Projects\Job1234\Shipping Info\Documents.

And I want it to look in C:\\Project Management\Bob Jones\Projects\Job1234\excelfile.xlsm.

Hope that clear it up a little.
 
Upvote 0
You can use the standard DOS/Windows parent folder "..\" syntax to go up 3 folder levels:
VBA Code:
    Dim otherWb As Workbook
    Set otherWb = Workbooks.Open(ThisWorkbook.Path & "\..\..\..\Excelfile.xlsm")

I have to go down folders, is there a way that can be done if the employee name length changes?
 
Upvote 0
Not quite. If where you want to save it is always 2 folders up and you use a string function like InstrRev to find a particular slash, that requires one expression. If it's not always 2, then something else - perhaps Instr if there's always x folders at the front of the path. A suitable and consistent pattern is needed, I think.
Consider using msoFileDialogFolderPicker and let user choose the location, or at least the starting location?
BTW, there is only one \ after C:, no?
 
Upvote 0
Not quite. If where you want to save it is always 2 folders up and you use a string function like InstrRev to find a particular slash, that requires one expression. If it's not always 2, then something else - perhaps Instr if there's always x folders at the front of the path. A suitable and consistent pattern is needed, I think.
Consider using msoFileDialogFolderPicker and let user choose the location, or at least the starting location?
BTW, there is only one \ after C:, no?

That's how I have it setup right now if for the user to select the file to grab from, I was just hoping there would be a way to auto select it. Not a huge deal, but save a couple of seconds each time.

And you are right, I should have only had the one \. I just typed it out to try and make things little more clear.

This code was to help fill out forms that have similar fields in each of them. So my idea was to fill all of them out once in a master file and then each time you need to fill out a form you click a button and it auto fills a lot of the info.

Thanks for all the help!!
 
Upvote 0
I don't see how I've helped much - unless you're going to run with the suggestions to use string functions by yourself.
Good luck!
 
Upvote 0
I have to go down folders, is there a way that can be done if the employee name length changes?
Your OP shows you want to go up 3 folders.

The file name where the excel file is located is C:\\Project Management\Bob Jones\Projects\Job1234\Shipping Info\Documents.

And I want it to look in C:\\Project Management\Bob Jones\Projects\Job1234\excelfile.xlsm.

That shows you want to go up 2 folders.

Say your main macro workbook is C:\Project Management\Bob Jones\Projects\Job1234\Shipping Info\Documents\main.xlsm, use this code in main.xlsm to open C:\Project Management\Bob Jones\Projects\Job1234\excelfile.xlsm.

VBA Code:
    Dim otherWb As Workbook
    Set otherWb = Workbooks.Open(ThisWorkbook.Path & "\..\..\Excelfile.xlsm")
    MsgBox "ThisWorkbook: " & ThisWorkbook.FullName & vbCrLf & "Other Workbook: " & otherWb.FullName
This should work regardless of the employee's name.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top