Copy data from folder files to 1 workbook

Montim

New Member
Joined
May 2, 2019
Messages
9
Hi all,

i want to copy data from multiple files in a folder to 1 workbook like this:


Workbook input1:
input1.jpg


Workbook input2:
YLp2gx5K

input2.jpg



Output merged data excel:
output.jpg


Any help would be very much appreciated. Sorry if it is not explained very well



 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What is the full path to the folder containing the source files? What is the extension of the source files (xlsx, xlsm)? Are those files the only files in that folder? What is the name of the source sheet in each source file and what is the name of the destination where the merged data will go?
 
Upvote 0
- Full path: c:\temp\
- Extension: .xlsx
- Not the only these files
- Default sheet name for all: Sheet1
- Destination file name and path: c:\temp\output.xlsx or .xlsm

And if this possible in a third excel, there are any chance to copy only the priced rows?


input3.jpg
 
Upvote 0
When you say " Not the only these files" do you mean that there are other files in that folder in addition to the files from which you want to copy the data?
 
Upvote 0
use PowerQuery aka Get&Transform with From Folder option then some little transforms to get a look like you want
 
Upvote 0
Soo, we have a lot of .xlsx files (input1.xlsx, input2.xlsx, input3.xlsx...input45.xlsx) in the c:\temp\ folder where i want merge/copy cell datas from files to a output.xlsm where i run the macro.

Example Input1.xlsx where i colored the cells and ranged:
input1.jpg


Input2.xlslx:
input2.jpg



Output.xlsx:
output.jpg




I found the next
 
Upvote 0
I found the next code on net, which is scan the files in folder, and get data, but this copy only columns, not specified cells and ranged cells:

Sub
getDataFromWbs()

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

'This is where you put YOUR folder name
Set fldr = fso.GetFolder("C:\temp")

'Next available Row on Master Workbook
y = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

'Loop through each file in that folder
For Each wbFile In fldr.Files

'Make sure looping only through files ending in .xlsx (Excel files)
If fso.GetExtensionName(wbFile.Name) = "xlsx" Then

'Open current book
Set wb = Workbooks.Open(wbFile.Path)

'Loop through each sheet (ws)
For Each ws In wb.Sheets
'Last row in that sheet (ws)
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Loop through each record (row 2 through last row)
For x = 2 To wsLR
'Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
ThisWorkbook.Sheets("sheet1").Cells(y, 1) = ws.Cells(x, 1) 'col 1
ThisWorkbook.Sheets("sheet1").Cells(y, 2) = ws.Cells(x, 2)
ThisWorkbook.Sheets("sheet1").Cells(y, 3) = CDate(ws.Cells(x, 3))
ThisWorkbook.Sheets("sheet1").Cells(y, 4) = ws.Cells(x, 4)
y = y + 1
Next x

Next ws

'Close current book
wb.Close
End If

Next wbFile

End Sub
 
Upvote 0
It's difficult to work with pictures. Perhaps you could upload a copy of the 3 files you displayed in Post #6 to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each for 'Sharing' and you will be given a link to each file that you can post here. This will make it easier to test a possible solution.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top