学以致用——VBA提取Excel中的备注(Extracting Comments from Excel with VBA)

需求：在Excel文件（工作簿workbook）中有数量很多的工作表（worksheet），每个工作表的A1单元格通过备注说明此表的作用。如何将这些备注提取出来？

代码：

Sub getComment()

Dim varComment As String
Dim c As Comment
Dim sht As Worksheet
Dim n As Integer
Dim objFSO As Object, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\[username]\Documents\test.txt", 2)

objFile.Write "" 'This clears the text file

With ThisWorkbook
    For Each sht In .Worksheets
        With sht.Range("A1")
            On Error Resume Next
            Set c = .Comment
            If Not c Is Nothing Then
                    n = n + 1
                    varComment = n & ". " & sht.name & "——" & c.Text & vbCrLf
                    Debug.Print varComment
                    objFile.Write varComment
                    
            End If
        End With
    Next
End With

objFile.Close

End Sub

效果：