Identify all excel files in a folder and then retrieve cells

[Maxpowerz]

I am trying to do an analysis of a large number of excel documents which are all forms that are filled out by managers. On a regular basis I will have a group of 10-20 excel workbooks that I need specific cells from. To clarify:

Workbook1.XLS
Workbook2.XLS
Workbook3.XLS

These workbooks are all inside the same folder.
I need a maco which pulls Cell H1 from each workbook.

Thanks in advance!

 

[stanleydgromjr]

Maxpowerz,

What is the full path where the following files will reside?
"On a regular basis I will have a group of 10-20 excel workbooks that I need specific cells from."

What is the sheetname that contains H1 that you want to copy from?

What is the workbook name, worksheet name, and range, where you want to copy the H1's to?

Will the summary workbook reside in the same folder as the above data files?

Have a great day,
Stan

 

[Maxpowerz]

Stan,

Sorry I believe I was a bit vague in my first post. Let me start by explaining the process more clearly.

1.) Each week I will be getting 10-20 data worksheets, of which I will not know the names, so the first step is to create code which identifies the worksheet names inside the Data Collection folder.

2.) Next I will need to collect the data from the cells A1, B6, and C9 from the first data worksheet and transfer each cell to the summary worksheet like so:
Data Summary
A1----> H1
B6----> H2
C9----> H3

3.) I will then need to collect data from the cells A1, B6, and C9 from the second worksheet and transfer them to the summary worksheet. (This is the same as step two except the data must be pasted one column over)
A1----> I1
B6----> I2
C9----> I3

4.) Finally, I must repeat this process until I have taken data from all the worksheets inside the folder.


Now to answer your questions.

The folder path is C:\Data Collection
The worksheet that I want to copy from must be determined by the VBA code since it will always be changing. The sheet inside the worksheet will always be titled "Project Profitability"

The workbook that I want to copy to will always be titled Summary.xls and the worksheet tab will be "Master Data Sheet"

The Summary.xls sheet will most likely be in the subfolder C:\Data Collection\Summary\Summary.xls

I hope this answers all of your questions with clarity. I'm a bit of a hack at VBA, but I came across this code which I think might be a start on step 1 except instead of the message box, creating the external link.

 Sub FindMe()
With Application.FileSearch
    .NewSearch
    .LookIn = "C:\YourPathHere\"
    .SearchSubFolders = True
    .Filename = "*.xls" 'or any other wildcard

    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

Thanks so much for your help on this, it's way over my head!

-Max

 

[stanleydgromjr]

Max,

"The WORKBOOK that I want to copy from must be determined by the VBA code since it will always be changing. The sheet inside the worksheet will always be titled "Project Profitability""

Ok, for each workbook in "C:\Data Collection", we want to copy into "Master Data Sheet":
A1----> H1
B6----> H2
C9----> H3

And, then for the rest of the workbooks, we copy to the columns to the right, I1, I2, I3, etc.....

It will be a lot easier if, after each weekly consolidation, that you move the weeks workbooks into another folder, say "C:\Data Collection\Completed". If you do not do this, you will have to change/update each week, the names of the weeks workbooks within the code - this will allow too many chances for errors.

What do you think?

Can you give me an idea as to the data in:
A1
B6
C9

Have a great day,
Stan

 

[stanleydgromjr]

Max,

Here you go.

Please TEST this FIRST in a COPY of your workbook.

It will be a lot easier if, after each weekly consolidation, that you move the weeks workbooks into another folder, say "C:\Data Collection\Completed". If you do not do this, I will have to change the code to accept an Array of the files names to proceess each week. You would then have to change/update each week, the names of the weeks workbooks within the code - this will allow too many chances for errors.

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Sub CreateSummary2()
'
'CreateSummary2 Macro
'Macro updated/created 08/17/2007 by Stanley D. Grom, Jr.
'
'Original code from:
'VBA & Macros for Microsoft Excel by Bill Jelen & Tracy Syrstad
'CombineWorkbooks()
'Page 300
'
    Dim CurFile As String
    Dim DestWB As Workbook
    Dim ws As Object                'allows for different sheet types
    Dim lngMasterCol As Long

    Const DirLoc As String = "C:\Data Collection\"

    Application.ScreenUpdating = False
    Set DestWB = ActiveWorkbook
    Sheets("Master Data Sheet").Select
    CurFile = Dir(DirLoc & "*.xls")
    lngMasterCol = 8
    Do While CurFile <> vbNullString
        Dim OrigWB As Workbook
        Set OrigWB = Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=True)
        Set ws = Sheets("Project Profitability")
        
        'A1----> H1  A1----> I1
        'B6----> H2  B6----> I2
        'C9----> H3  C9----> I3
        With ws
            DestWB.Sheets("Master Data Sheet").Cells(1, lngMasterCol) = ws.Range("A1")
            DestWB.Sheets("Master Data Sheet").Cells(2, lngMasterCol) = ws.Range("B6")
            DestWB.Sheets("Master Data Sheet").Cells(3, lngMasterCol) = ws.Range("C9")
        End With
        lngMasterCol = lngMasterCol + 1
        OrigWB.Close SaveChanges:=False
        CurFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Please TEST this FIRST in a COPY of your workbook.

Then run the 'CreateSummary2' macro.

Hava a great day,
Stan

 

[Maxpowerz]

Thanks!

This works perfectly, thank you so much!

-Max

 

[BonanzaNLD]

Can I also use this macro for copying rows from different files into one sheet?

I've got around 130 different xls files, all having the same sheet "Test Results". From that sheet, I want to copy all rows into my master file, and add all the the rows from all the other 129 xls files into it. So it basically contains all the rows from all the files in one Master Sheet.