VBA Copy cells from multiple workbooks from variable folders and filenames

marg0

New Member
Joined
Sep 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,638
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.

VBA Code:
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
 
Solution

marg0

New Member
Joined
Sep 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 6, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:)
 

Forum statistics

Threads
1,181,454
Messages
5,930,005
Members
436,716
Latest member
MiroUna

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