VBA to Create a Report of Cell Comments, Take 2

[lneidorf]

Hi there.

I'm attempting to create a user-friendly macro-enabled spreadsheet ("Comments Recap v2.xlsm") that will generate a report of the Excel cell Comments in selected Excel workbooks.

The goal is for my users to open my file, click a button, be promoted to select wither a single file or a folder full of files. After that, the file(s) will be analyzed, and a report on their cell comments will appear on a tab of my file.

The report, appearing on a tab entitled "Comment Recap", lists File Name, Sheet Name, Cell Address, Cell Value, and the actual Comment content.

Where there are multiple files, the report of their cell comments will appear on that same "Comment recap" tab, appended beneath any prior data.

I've been playing around with this a bunch, but no success as yet.

Here's my most recent code, this version intended to prompt the user to select a single file for evaluation:

Sub ShowCommentsAllSheetsRevised3()
'Adapted from https://www.extendoffice.com/documents/excel/679-list-all-comments-workbook.html
Dim commrange As Range
Dim rng As Range

Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, ws As Worksheet
Dim Lastrow As Integer
Set swb = ThisWorkbook
Set sws = swb.Worksheets("Comment Recap")

    'Open File for Evaluation
    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS*), *.XLS*", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Set wb = Workbooks.Open(Filename:=fNameAndPath, UpdateLinks:=0)

sws.Range("A1").Resize(1, 5).Value = Array("File Name", "Sheet Name", "Cell Address", "Cell Value", "Comment")
On Error Resume Next
For Each ws In Application.ActiveWorkbook.Worksheets
    Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
    If Not commrange Is Nothing Then
        i = sws.Cells(Rows.Count, 1).End(xlUp).Row
        For Each rng In commrange
            i = i + 1
            swb.Cells(i, 1).Resize(1, 5).Value = Array(ActiveWorkbook.Name, ws.Name, rng.Address, rng.Value, rng.Comment.Text)
        Next
    End If
    Set commrange = Nothing
Next

End Sub

I create many utilities for use by my colleagues. They typically contain a tab ("Dashboard") where there are instructions and options as well as buttons for my users to run a macro/macros across other files. With my code, I often struggle with making references to other files intended for evaluation / processing. I think that's a problem with my code here.

I've got additional code for running code across the contents of an entire folder. I'm fairly confident that I can adapt this for that purpose if I can get this to work.

Any help you can offer would be most appreciated.

Thanks!