Retrieve comments from an Excel file

[ghosh]

Is there a way to retrieve comment from Excel 2013 xlsx worksheets using SAS Enterprise Guide?   Perhaps through xml? 

When I try to save the xlsx file in xml format I get an error message "cannot save XML data because the workbook does not contain any XML mappings".  However, I am not sure how I would create mappings for comments since they occur in several columns.

Thanks

[andreas_lds]

---

@ghosh wrote:

Is there a way to retrieve comment from Excel 2013 xlsx worksheets using SAS Enterprise Guide?   Perhaps through xml? 

When I try to save the xlsx file in xml format I get an error message "cannot save XML data because the workbook does not contain any XML mappings".  However, I am not sure how I would create mappings for comments since they occur in several columns.

Thanks

---

You could extract the xlsx file, search for the xml-file containing the comments (could be more than one) and read that xml-file using libname. I would do nearly anything to avoid such a programming task 😉

But there seems to be no other way to import the comments.

[ghosh]

I did not understand how I could extract the xlsx until I read the message s_lassen below. However, thank you for the lead.

[ScottBass · Accepted solution]

I Googled "excel vba extract comments".

Here is one hit:  https://trumpexcel.com/get-list-of-comments-in-a-worksheet-excel/

Perhaps this will get you what you need?

[ScottBass]

Or Powershell?

https://stackoverflow.com/questions/44811375/reading-comments-from-excel-file-with-powershell

I'll leave it to you to Google Python, etc 🙂

[ghosh]

Thanks for both suggestions, I did see these approaches when I had googled the problem, still hoping to find a SAS solution if possible before going the VBA route.

[ghosh]

This works quite well. Thanks for the link

[s_lassen]

I think an Excel file is actually a zipped xml-file. Meaning that you can get all the stuff out of the file if you allocate it as a ZIP filename and read it as an XML or a text file. But I cannot help you with the details of where to find the comments.

You could start by renaming/copying your .XLSX file to .ZIP, extract it to an XML-file, and open it in your favourite text editor (UltraEdit and Notepad++ come to mind as good possibilities).

[ghosh]

s_lassen  Thanks for this lead, I did not know that a xlsx file was actually a zip container.  When I renamed an xlsx file to zip and extracted all the xml files, I found comments are stored in several xml files. 

Now I am trying to establish a link between the xml files and the worksheets and cell refs. 

Thanks again.