community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

Input contents of excel comments

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

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?

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!

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 :)

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.

Magnetar
Magnetar

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