macro: paste cell from specific files to 1 general file

HI
It is possible to pull data from multiple files to a single file. Pl give me more details of which cells you want pulled (from which sheet (name)) and where do they go in general book
Ravi

Someone provided me with a code that almost works (problem is that it copies and pastes randomly):

" requires that you copied all 80+ files you want to extract data from in one folder (you need to provide the path name for the constant SOURCE_FOLDER). The macro opens each of the workbooks in that folder, extract the data from cell B1 and copy it to the next empty cell in column A of the active worksheet in the workbook you are running the macro from."

Please Login or Register  to view this content.

Option Explicit
Const SOURCE_FOLDER = "C:\..."
Sub AggregateDataFromFiles()
Dim fs As Object
Dim objFolder As Object
Dim objFolderName As String
objFolderName = SOURCE_FOLDER
Dim filePath As String
Dim objFile As Object

Dim targetWb As Workbook
Dim lastrow As Long
lastrow = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(objFolderName)
For Each objFile In objFolder.Files
filePath = objFolderName & "\" & objFile.Name
Set targetWb = GetObject(filePath)
targetWb.Worksheets(1).Range("B1").Copy Destination:=ActiveSheet.Range("A" & lastrow)
lastrow = lastrow + 1
targetWb.Close (False)
Next
End Sub

Please Login or Register  to view this content.

However, the copying and pasting is happening randomly into the summary sheet. But, I'd like that it grab the cell and paste it in the same order every time (from the first file in the folder, follower by the second etc.). Right now it's picking a random file in the folder that's specified and moving it into the column that's specified. Is there a way for it to copy first from the first file in the folder (the one at the very top of the folder window) followed by those below?

HI
Save the attached file inside the folder containing 80+ files. Assuming sheet name is sheet1, run the macro in the attached file. It lists files in col A and B1value in col B. How do you want the files arranged (what is first and second in your case)
Ravi

Hi Ravi,


That's great! Thank you

right now it lists the name of the file in column A (which is great because it tells me what the value is about) and the column B values are all 0.

Column B values should be equal to Column AP, cell 100 in Sheet 1.
Column C Column AP, cell 101
Column D AP 102

E AP 104
F AP 105
G AP 106

H AP 108
I AP 109
J AP 110

K AP 112
L AP 113
M AP 114

N AT 48


Would that be doable?

Thank you so much for your help.

Hi
Try the modified codes
Ravi

Hi Ravi,

It's only copying from the first 3 files right now.
And, it's not copying the values from AP112, AP113 and AP114.

Right now, it's doing:

Column B: AP100
Column C: AP101
Column D: AP102
Column E: 0
Column F: AP104
Column G: AP105
Column H: AP106
Column I: 0
Column J: AP108
Column K: AP109
Column L: AP110
Column M: 0
Column N: AT48

If it's writing "0" for the cell that it skips, then maybe it can continue copying to Column N, O and P from AP112, AP113 and AP114 and then Column R AT48?

Thank you

Also, it writes the name of the directory in Column A row 1; and the name of the file "jy2009" in column A row 79.

And, it pastes the first 4 files in the folder at the end.

Thank you

Hi
I have modified the codes to add changes you suggested. try it
Ravi

amazing! it's doing everything perfectly.

thank you

Hi
Are you using xl2007 and still getting it perfect?
Ravi

Hi Ravi,

Well, it's still doing some funny things: it's putting the first file's values into row 32 (where the 31st file's values are supposed to be) and the 31st file is in the 61st row, and the 60th file is in row 69. And the file that's supposed to be in row 69 is in row 74.

Everything is copying and pasting correctly, except for the first file in the folder.

I'm not sure why those strange cases are happening, but it's not a big deal because I can just insert the row where it's supposed to be. And, the first file values I can copy and paste manually.

Hi Ravi,

I just redid it with another 80 files in a different folder, and everything worked fine. The only extra thing it does is paste "JY2009.xls" in row 78.

So, I think it was something in one or a few of the first set of 80 files that created the issue.

Thank you again for your help