Reading the XML of a workbook

Hi,

By changing the extension of an xlsm file to .zip and extracting that zip folder we can see a file named "comments1.xml"

Is there a way to read comments from that file without zipping workbook/without opening the workbook? using VBA or any other language?

Any one please...?

Not with VBA.
You might find another language has libraries to read a zip file

Why can you not open the workbook?

The reason not to open is very common one "A lot of files are listed in the master workbook and need to quickly read the comments from specific cells of the closed workbooks"

upto 1000 books will take time to
1:Lopen
2:read comment
3: clos
and those files are also an xlsm template which runs a lot of code on opening.

Later posted here.

And posted : here today.

Are you sure there's only ever one sheet in the workbooks?

and those files are also an xlsm template which runs a lot of code on opening

You can disable auto-open macros before opening the workbooks; see Application.AutomationSecurity.

Originally Posted by rorya

Are you sure there's only ever one sheet in the workbooks?

Thanks rory for the response.
No I have multiple worksheets in the workbook but need to read comments from only one specific sheet currently.

Then you can't just extract comments1.xml as it may not be the correct sheet's comments. You either need to know the index number of the sheet whose comments you want, or you'll need to parse all the sheetnn.xml files to find the correct one. Then you'll need to find the correct rels file to workout which comment file belongs to it.

Thanks again

If I manage to have only one worksheet in the task files, the name of the sheet is same in all task files,then what will be the way to extract comments from that sheet?

I have a specific range in that sheet that comments of which are needed to read.

Here's some very rough code to get you started.

Please Login or Register  to view this content.

Thank you very much rory for your precious time and effort.

I examined my Task Files they do have multiple sheets but with .zip I see my required Comments are alway in comments3.xml

What changes do I need in the script if I have a confirmed name of comments.xml file for each Task files?

Since you have Office 2016, here's another approach using Get & Transform (PQ).
Assuming that you have workbooks changed to .Zip extension already...
You can use PQ to query it.
EDIT: Actually it does not need to have .Zip extension. You can simply read xlsx file using fnUnzip.

Ex: Book1.Zip is stored in "C:\Test" folder. Extracting comment text from comments3.xml

1. Follow link below and copy custom function as blank query named "fnUnzip" in advanced editor.
http://sql10.blogspot.com/2016/06/re...erquery-m.html

2. Add another blank query and use following as source.
= File.Contents("C:\Test\Book1.zip")

3. Use fnUnzip custom function to read the content of zip file in advanced editor. Complete "M" should look like below at this point.

Please Login or Register  to view this content.

2.JPG

4. Apply filter to "FileName" column using "comments3.xml" as condition.

5. Right click on result and choose "xml"

6. In advance editor, add "Table = #"Imported XML"{1}[Table]{0}[Table]{0}[text]" line. "M" should look like below.

Please Login or Register  to view this content.

7. Split the resulting column using custom delimiter "Comment:" at right most delimiter.

8. Remove 1st column from result and trim the 2nd column. You are left with comment string from comments3.xml
1.JPG

Complete "M" code.

Please Login or Register  to view this content.

I assumed you only had one comment in comments3.xml. If you need further help. I'd recommend that you upload sample zipped file(s).