We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Input contents of excel comments

Vivien_Pascu
6 - Meteoroid

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

8 REPLIES 8
Aidan_K
11 - Bolide

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?

Vivien_Pascu
6 - Meteoroid

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!

Aidan_K
11 - Bolide

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

neilgallen
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.

Claje
14 - 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.

 

 

Marenza
5 - Atom

Hi Neil,
Would you mind creating and attaching a sample workflow how to get comment/notes from excel .zip file to output file? I tried to follow your advice but I was stuck at the beginning. I'd appreciate it!

 

neilgallen
12 - Quasar

see attached. This is a quick mockup but should get you started.

Marenza
5 - Atom

Hi Neil, many thanks for your prompt response,
I've tried to build a workflow based on your advice however I haven't received expected outcome, In the output file I've got references of comments and threaded comments only.I'm not sure if my Alteryx version 2019.3.5 doesn't support this action

I will be consolidating details from many input files (Excel) with Union or Join tools to bring both, values and comments to one output file. Would you mind checking what's wrong in my workflow? Thanks a mill, Marzena 

Labels