This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm not sure how to do this in Alteryx but it can be done in excel - Using VBA you can add all comments to a new sheet in excel and then use this new sheet as a new input for Alteryx? Would this work for you?
Open vba window and create new module (ensure it is going to save in your "personal.xlsb" file.
Paste the following code and save.
(everything between the ---- lines
Dim commrange As Range Dim rng As Range Dim ws As Worksheet Dim newWs As Worksheet Set newWs = Application.Worksheets.Add newWs.Range("A1").Resize(1, 4).Value = Array("Sheet", "Address", "Value", "Comment") Application.ScreenUpdating = False On Error Resume Next For Each ws In Application.ActiveWorkbook.Worksheets Set commrange = ws.Cells.SpecialCells(xlCellTypeComments) If Not commrange Is Nothing Then i = newWs.Cells(Rows.Count, 1).End(xlUp).Row For Each rng In commrange i = i + 1 newWs.Cells(i, 1).Resize(1, 4).Value = Array(ws.Name, rng.Address, rng.Value, rng.Comment.Text) Next End If Set commrange = Nothing Next newWs.Cells.WrapText = False Application.ScreenUpdating = True End Sub
if you run this it will create a new sheet in your workbook with the cell reference, the value in the cell and the comment text in separate columns.
You can add the macro to a button on your excel ribbon to execute more quickly (search google for simple walk through if needed).
Unfortunately I cannot attach a macro enabled file to the post
I was going to start going down the path of recommending the Python tool, and the library "openpyxl" to accomplish this, but @neilgallen's solution is a really good one.
Something to know also is that you shouldn't need to change the extension to use this with Alteryx. If you configure Alteryx to read the excel file as a "Zip Archive" (*.zip) you can pick out the file (I believe it is xl/comments1.xml) and read it in as XML.