Extracting comments from Excel

[robertdseals]

Is there a way to extract comments from Excel cells (even if the comments are hidden or not) and use them in a power bi visual? Thanks in advance for any assistance.

 

[Wookiee]

I can't really speak to the use of the data in PowerBI, but I put together this macro to write all the comments to a new blank workbook.

Sub ExtractComments()
'Crafted by Wookiee at MrExcel.com

Dim lngCount  As Long
Dim shpX      As Shape
Dim wkbNew    As Workbook
Dim wksTab    As Worksheet

On Error GoTo GetOut

Set wksTab = ActiveSheet
Set wkbNew = Workbooks.Add

With wksTab

   For Each shpX In .Shapes

    If shpX.Type = msoComment Then
      
      lngCount = lngCount + 1
      wkbNew.Sheets(1).Range("A" & lngCount) = shpX.Name
      wkbNew.Sheets(1).Range("B" & lngCount) = shpX.AlternativeText

    End If
   
   Next shpX
   
End With


GetOut:
On Error GoTo 0


End Sub