How can i make path of folder dynamic

[A9kurs]

Hi
I want to pick the path from cell but gives me error.
earlier i have to update it in code so i want if i update it cell that will be good

Please help in this Set dirObj = mergeObj.Workbooks("Planned_Inventory_Transaction_Macro").Worksheets("Summary").Range("O1")

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Workbooks("Planned_Inventory_Transaction_Macro").Worksheets("Summary").Range("O1")
'Set dirObj = mergeObj.Getfolder("C:\Users\Ankur.Sharma\Desktop\082919 PIT\Raw Data")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:Y" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(2).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
bookList.Close
Call Fill_Down
Call Date1
Call Copypaste1
Next
MsgBox "Import Done"
End Sub

 

[Marc L]

Hi !​

​

You try to mix two very differents objects in one ‼ (Set Dirobj = mergeObj.Workbooks)​

​

As FSO is slower, it's better and easier for a beginner to use the Dir VBA function, see its sample in VBA help.​

​

Using the text of a cell within a string is like "beginning of the string" & Range.Text & "end of the string" …​

​