Consolidate Single Cell from Multiple Files Into List

[MadExcelMax]

Hello,I've been reading and watching videos and I'm just lost.What I want to do:I have 30 files named 01-30I need cell M2 from all 30 files.All 30 M2 cells are text comments.I need them pasted to a separate file.In a list top to bottom (so assuming A1 starts, next comment on a2, then a3, etc.)Thanks in advance!

 

[RudiS]

1. Are the comments typed into cell M2 or are they actual comments (as in Insert>Comment, and you need the text within the comment?)

2. Are the "comments" are on sheet 1 of each workbook?

3. Are all the workbooks in a single folder and can a macro simply iterate through ALL the files in the folder to collect the comments?

 

[MadExcelMax]

The comments are typed into the cell itself.

Each workbook is is a class roster. 30 students by name from row A2 - A31. Column M is for comments. So for student 1 the comment is in M2.

Row 1 is reserved for the columns name.

All the workbooks are in a single folder.

thank you so much for helping!

 

[RudiS]

It's a pleasure. Try this macro:

Copy the code
In a blank new workbook, press ALT+F11 (to open the VB Editor)
From the menu bar in the VB window, choose Insert > Module
Paste the code into this new blank module
Close the VB Editor
In Excel, press ALT+F8, select the macro called GetComment_M2 and choose Run

The macro will prompt you to browse for the folder containing all the files
Browse to this folder, select it and choose OK

The list of comments should appear on sheet 1 of the workbook containing the macro

Sub GetComment_M2()
Dim myFolder As Object
Dim sPath As String
Dim sExt As String
Dim sFile As String
    
    Application.ScreenUpdating = False
    Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With myFolder
        .Title = "Browse for folder containing the files to process..."
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        sPath = .SelectedItems(1) & "\"
    End With
    With ThisWorkbook.Sheets(1)
        .Columns(1).Clear
        sExt = "*.xls*"
        sFile = Dir(sPath & sExt)
        Do While sFile <> ""
            If sFile <> ThisWorkbook.Name Then
                Workbooks.Open Filename:=sPath & sFile
                    .Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = _
                        Workbooks(sFile).Sheets(1).Range("M2").Value
                Workbooks(sFile).Close False
            End If
            sFile = Dir
        Loop
        With .Range("A1")
            .Value = "All Comments"
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
        .Columns("A:A").EntireColumn.AutoFit
    End With
    Application.ScreenUpdating = True
    MsgBox "Completed processing each workbook in folder: " & vbNewLine & sPath, vbInformation
End Sub

 

[MadExcelMax]

Wow... amazing! Thanks so much.

 

[RudiS]

Glad to help

 

[MadExcelMax]

Just one more quick question regarding this script.If I wanted to run this on a worksheet that has a second page beginning on A25 and have the comments propagate from A25 onward much like how it currently does from A1 how would I do that?

 

[RudiS]

Try this (I trust I understood the request correctly);

Note: The macro assumes there is already a second sheet in the workbook where the comments must collect...

Sub GetComment_M2()
Dim myFolder As Object
Dim sPath As String
Dim sExt As String
Dim sFile As String
    
    Application.ScreenUpdating = False
    Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With myFolder
        .Title = "Browse for folder containing the files to process..."
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        sPath = .SelectedItems(1) & "\"
    End With
    With ThisWorkbook.Sheets(2)
        .Range(Cells(25, 1), Cells(Rows.Count, 1)).Clear
        .Range("A25").Value = "All Comments"
        sExt = "*.xls*"
        sFile = Dir(sPath & sExt)
        Do While sFile <> ""
            If sFile <> ThisWorkbook.Name Then
                Workbooks.Open Filename:=sPath & sFile
                    .Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = _
                        Workbooks(sFile).Sheets(1).Range("A1").Value 'Range("M2").Value
                Workbooks(sFile).Close False
            End If
            sFile = Dir
        Loop
        With .Range("A25")
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
        .Columns("A:A").EntireColumn.AutoFit
    End With
    Application.ScreenUpdating = True
    MsgBox "Completed processing each workbook in folder: " & vbNewLine & sPath, vbInformation
End Sub

 

[MadExcelMax]

Thank you again Rudi.

I hope this is not against any rules but would you be willing to help me just a little bit more further? I am willing to pay you a stipend for all your help and time.

Zippyshare.com - Project.7z

I have the script you provided me orignally and the two forms I am working with.

Peer Evals BA#31 Module II.xlsx
This sheet has column A with the name of each person.
Columns B - L will have a number in the cell from 1 - 5 (a rating system)
For Name 01 in column A2 the ratings for that name will all be in columns B - L in row 2.
Comments for Name 01 will be in M2.

There will be 29 total separate files like this one.

Basic Academy Final Peer Evaluation Report.xlsx
This sheet is where the script will run to gather comments on the second worksheet from all 29 copies of the above file. The script to do this runs perfectly.

What I'm wondering is if there is a way to make a script to:
take the name and place it in E1.
take all 29 values of B2, average them, and put that in B18 and so on for each category.

I can change the values and run a separate script for each name. For instance with the comment script I am just going to change the range M2 to M3 and so on for each separate entry.

Thanks!

 

[RudiS]

Hi,


First, this is a community forum. We all log on to support and assist each other. There is no need to offer payment for help provided


I have downloaded the sample files you supplied and read through your description of what you need, however, I am a bit unclear on one or two aspects of the filesand the compilation of the data.


Your Report file seems to gather info for a single recruit (evaluation sheet, cell E2), but the averages you request in row 18 span over multiple names (Name 01 to 29) in a single Peer Evals file. On the other hand, the comments you collect on sheet 2 collect comments for Name 01 (cell M2 in Peer Evals) over 29 separate workbooks (*.xlsx files stored in a folder). I'm not sure how this all ties into each other??


What will happen for Recruit 2? Is there a different Report File for this person?


Bottom Line:
The fact that you have 29 names on a single sheet in a single Peer Evals file, but you also have 29 separate Peer Eval workbooks (*.xlsx files in a folder) is throwing me off and I'm trying to make sense of the process you follow to get all this info into a Report file.


Please clarify the process again. (Apologies for my fogginess to understand this!)