CODEWITHSUNDEEP
vbaexcel
Dan C
Dan C

Reputation: 19

Open a file/folder from a specified cell

I would appreciate any help with this! I'm trying to figure out the correct code in order to pull from folder "2017" (shown below) dynamically. Within this folder there are many excel workbooks. From there the vba copies and pastes a range of cells into the original workbook. So far the code is very manual, where whenever I try to switch the folder origin I have to manually change it within the code.

Would someone be able to assist me on editing the code to be dynamic? For example: what I'm hoping to achieve is that if the folder's name is in cell A1. Then the vba code would open the folder and pull the workbooks from whatever name was in cell A1.

Please let me know if there's any additional information I could provide!

Thank you in Advance!

  Sub ArrDepMerge2017()


Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim c As Long

Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.Getfolder("2017")
Set filesObj = dirObj.Files

For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    c = c + 1
    bookList.Sheets(1).Range("F37:F53").Copy ThisWorkbook.Worksheets("2017 Data").Cells(1, c)
    bookList.Close

End Sub

Upvotes: 1

Views: 240

Answers (1)

QHarr
QHarr

Reputation: 84465

You assign a cell value to a variable e.g. path and then use that within your GetFolder. Also, add the Next everyobj when using For Each.

Sub ArrDepMerge2017()

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim c As Long
Dim path as String

path = ThisWorkbook.Sheets("mysheetname").Range("myrange")

Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.Getfolder(path)
Set filesObj = dirObj.Files

For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    c = c + 1
    bookList.Sheets(1).Range("F37:F53").Copy ThisWorkbook.Worksheets("2017 Data").Cells(1, c)
    bookList.Close
Next everyObj ' this was missing

End Sub

Upvotes: 1

Related Questions