File Path from cell value

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
Hello All

I am trying to modify this piece of code to point to a cell value for the file path instead of hard coding the path which I would have to change for different users.


Code:
Sub Merger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Sheets("Pipeline").Select
Rows("7:1000").ClearContents
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder([B]"C:\Users\fred\Desktop\Board\"[/B])
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

I have this piece of code that works well with the cell value file path

Code:
Dim fromPath As String

[B]fromPath = Sheets("Details").Range("D2")[/B]
'   
If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Any idea's please on how to modify the code
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Does this give you the correct path for every user?
Code:
Dim fPath As String
fPath = "C:\Users\" & Environ$("UserName") & "\Desktop\Board\"
MsgBox fPath
If it does ..
Code:
Dim fPath As String
fPath = "C:\Users\" & Environ$("UserName") & "\Desktop\Board\"
Set dirObj = mergeObj.Getfolder(fPath)

NOTE - run the code unchanged
- the current user's windows uername is looked up by VBA in this line
Code:
fPath = "C:\Users\" & Environ$("UserName") & "\Desktop\Board\"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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