Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Input contents of excel comments

Highlighted
Alteryx Partner

Hi All,

 

Do you know if there is any way to input the Excel comments with the sheet as separate columns? I tried to search for some answers, but didn't succeed so far...

 

Thanks a lot!

Vivien

Highlighted
9 - Comet

Hi @Vivien_Pascu,

 

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?

Highlighted
Alteryx Partner

Hi Aidan,

 

Thanks for the answer! I will need to check if I can work around with this, but it is definitely an option... thanks a lot!

Highlighted
9 - Comet

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

-------------------

Sub ShowCommentsAllSheets()

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 hope this helps :)

Highlighted
12 - Quasar

One other option would be to parse this from the xml within the file. All MS Office documents are basically just zipped xml files. 

 

If you change the extension of your excel file from '.xlsx' to '.zip' you can view the contents, one of which will be a file named "comments.xml"

 

In my test scenarios that file was located in a folder named "xl"

 

The comments file is just an xml document that contains the cell reference and comments, as well as the author. This could be easily parsed using alteryx.

Highlighted
ACE Emeritus
ACE Emeritus

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.

 

 

Labels