VBA Copy cells from multiple workbooks from variable folders and filenames

[marg0]

Hello kind people of MrExcel!

I have been searching for this problem a lot in here, but I haven't found yet anything that might help me (or maybe I just can't combine the pieces together).

I have multiple folders that have various files within them. I want to copy a constant range of cells from specific files from each folder, and combine them into columns in a master file. For example:
I have folder JohnA with multiple files, but I only need file JohnWhatever.xls; copy range A1:B4 from Sheet1 of JohnWhatever.xls and paste into Master file A:B column.
Then go into next folder MaryB with multiple files, but only get file MaryWhatever.xls; copy range A1:B4 from Sheet1 of MaryWhatever.xls and paste into Master file A:B column, below the data from the first file.
So on for many many folders. I have the list of folder names/paths as well as file names.

Perhaps it's not relevant to define the folder names/paths, but I don't know if it's possible (or feasible) for the code to go through all the subfolders and look for each of the files that I need.

I thought I'd build a macro to go through each folder with the path specified in a range, and then open the specific workbook based on the name from another range, and copy cells A1:B4 into a column on the master file. Tried to look for this specific solution but can find only partial solutions. As you can imagine, it's not going very well as evidently my "code frankensteining" skills aren't as good as I'd hoped :D

Would be awesome if anyone would be able to help me

 

[John_w]

I thought I'd build a macro to go through each folder with the path specified in a range, and then open the specific workbook based on the name from another range, and copy cells A1:B4 into a column on the master file.

On Sheet1, with the paths in column A, starting at A2 and the workbook file names (including extension) in column B starting at B2, this macro copies A1:B4 cell values from the first sheet in the workbooks to Sheet2. Different code is needed if you want to copy cell formats as well.

Public Sub Copy_Range_From_Workbooks()

    Dim fileCells As Range, fileCell As Range
    Dim destCells As Range, r As Long
    Dim fromFile As String, fromWorkbook As Workbook
    
    With ActiveWorkbook
        With .Worksheets("Sheet1")
            Set fileCells = .Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
        End With
        Set destCells = .Worksheets("Sheet2").Range("A1:B4")
    End With
    
    Application.ScreenUpdating = False
    
    r = 0
    For Each fileCell In fileCells
        fromFile = fileCell.Value
        If Right(fromFile, 1) <> "\" Then fromFile = fromFile & "\"
        fromFile = fromFile & fileCell.Offset(, 1).Value
        Set fromWorkbook = Workbooks.Open(fromFile)
        destCells.Offset(r).Value = fromWorkbook.Worksheets(1).Range("A1:B4").Value
        fromWorkbook.Close SaveChanges:=False
        r = r + 4
        DoEvents
    Next
   
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[marg0]

It worked perfectly, thank you so much for your quick response! (I had to do some experiments to adjust to my specific needs, but your solution was exactly what was needed!)

 

[Farrakh]

Hello kind people of MrExcel!

I have been searching for this problem a lot in here, but I haven't found yet anything that might help me (or maybe I just can't combine the pieces together).

I have multiple folders that have various files within them. I want to copy a constant range of cells from specific files from each folder, and combine them into columns in a master file. For example:
I have folder JohnA with multiple files, but I only need file JohnWhatever.xls; copy range A1:B4 from Sheet1 of JohnWhatever.xls and paste into Master file A:B column.
Then go into next folder MaryB with multiple files, but only get file MaryWhatever.xls; copy range A1:B4 from Sheet1 of MaryWhatever.xls and paste into Master file A:B column, below the data from the first file.
So on for many many folders. I have the list of folder names/paths as well as file names.

Perhaps it's not relevant to define the folder names/paths, but I don't know if it's possible (or feasible) for the code to go through all the subfolders and look for each of the files that I need.

I thought I'd build a macro to go through each folder with the path specified in a range, and then open the specific workbook based on the name from another range, and copy cells A1:B4 into a column on the master file. Tried to look for this specific solution but can find only partial solutions. As you can imagine, it's not going very well as evidently my "code frankensteining" skills aren't as good as I'd hoped :D

Would be awesome if anyone would be able to help me

You have to do nothing.
No Vba required

Just follow the below some easy steps.

1. Make sure that your files have same table name (from where you want to copy the data)
2. Open you excel file where you want to get/copy the data
3. Click "Data"
4. Click "Get Data"
5. Select "From Folder"
6. Select your main Folder and click Open (this main folder may have contained many subfolders and files)
7. Excel will display a list of all files.
8. Click "Combine" and select "Combine & Load To"
9. Select Sample File "First File"
10. Click "Petameters"
11. Select the Table name which you want to import. On the right side, you can view the table data.
12. Click "OK"
13. Select "Table" option, and change nothing and click "Ok"
14. Excel will load the data from all files.
15. This data is linked to your selected folder, whenever you create a new file or edit any file, data will be updated in this file.

Thank you

 

[marg0]

You have to do nothing.
No Vba required

Just follow the below some easy steps.

1. Make sure that your files have same table name (from where you want to copy the data)
2. Open you excel file where you want to get/copy the data
3. Click "Data"
4. Click "Get Data"
5. Select "From Folder"
6. Select your main Folder and click Open (this main folder may have contained many subfolders and files)
7. Excel will display a list of all files.
8. Click "Combine" and select "Combine & Load To"
9. Select Sample File "First File"
10. Click "Petameters"
11. Select the Table name which you want to import. On the right side, you can view the table data.
12. Click "OK"
13. Select "Table" option, and change nothing and click "Ok"
14. Excel will load the data from all files.
15. This data is linked to your selected folder, whenever you create a new file or edit any file, data will be updated in this file.

Thank you

Hi Farrakh and thank you so much for your reply!

Seems like a pretty neat solution as well! Unfortunately, it wouldn't work in my case, since the source files are already populated by other people and there are no tables present, just information scattered in different cells. It's good to know though, there may be other cases when I can use this solution