VBA Code to path based on a cells contents.

[Curtisyoung78]

I have a VBA Code that retrieves info from a path that is in the VBA coding itself. I need to be able to let other users change the path regularly with having to know how VBA works. I would like it to be able to read the path from a cell where the user can paste the path (cell L2) and not have to have the developer tab and chance altering the VBA negatively. Below is the code i have, as you can see it has the path in it. Ive tried a few things but my limited VBA knowledge has come up short. Please help. Thank you in advance.

Sub MergeFiles_v1()


Dim wkb As Workbook
Dim LR As Long
Dim LC As Long
Dim arr() As Variant
Dim filename As String
Const path As String = "T:\Curtis\Pile Data" ' would like to paste to cell L2 and have it read the contents as the path

With Application
.EnableEvents = False
.ScreenUpdating = False
End With




filename = Dir(path & "\*.xls", vbNormal)
If Len(filename) = 0 Then Exit Sub

Do Until filename = vbNullString
If Not filename = ThisWorkbook.Name Then
Set wkb = Workbooks.Open(path & filename)
With wkb
With .Sheets(1)
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
arr = .Cells(2, 1).Resize(LR - 1, LC).Value
End With
.Close False
End With
Set wkb = Nothing

With ActiveWorkbook.Sheets(1)
LR = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
.Cells(LR + 1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
Erase arr
End With
End If
filename = Dir()
Loop

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

 

[JLGWhiz]

path equates to the value "T:\Curtis\Pile Data" so

Range("L2") = path

should put that value in the cell. But why re-invent the wheel, just use the constant 'path' where you need the value. I suppose it could be needed for visual purposes. Or maybe I don't understand the issue.

 

[Fluff]

@JLGWhiz
My reading is that the OP wants it the other way round.
In which case

path = Range("L2")

 

[Curtisyoung78]

Worked perfectly, thank you.

 

[Fluff]

Glad one of us could help & thanks for the feedback.