Read Office Open XML (OOXML) part Using Excel VBA

Question

I'm tying to read the XML parts of an Excel file using VBA, however, not able to find any resources to get started. Is this possible? I've only seen solutions with .NET.

I created a CustomXMLPart and read/write from/to what I created. However, I'm trying to read the standard XML parts created by Excel. Specifically I'm interested in reading the contents of the "workbook.xml" part.

How would I read the sheet and cell reference associated with the 'MyNamedRange' reference using Office OpenXML?

I know there are other ways to do this specific example, but I'm looking for the OpenXML way and the following is just an example.

Sample 'workbook.xml':

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <fileVersion rupBuild="4507" lowestEdited="4" lastEdited="4" appName="xl"/>
    <workbookPr defaultThemeVersion="124226" codeName="ThisWorkbook"/>
    <bookViews>
        <workbookView windowHeight="5970" windowWidth="11475" yWindow="45" xWindow="480"/>
    </bookViews>
    <sheets>
        <sheet r:id="rId1" sheetId="1" name="Sheet1"/>
        <sheet r:id="rId2" sheetId="2" name="Sheet2"/>
        <sheet r:id="rId3" sheetId="3" name="Sheet3"/>
    </sheets>
    <definedNames>
        <definedName name="MyNamedRange1">Sheet1!$A$1</definedName>
        <definedName name="MyNamedRange2">Sheet1!$A$2</definedName>
    </definedNames>
    <calcPr calcId="125725"/>
</workbook>

It would help if you would explain what it is that you are trying to achieve, i.e. why do you need to read Open XML using VBA? Your questions looks like a classical [X Y problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)... — Dirk Vollmar, Oct 01 '16 at 19:58
Thanks for responding. I've added an example of something I'd like to try to do. — ptownbro, Oct 01 '16 at 20:21

score 1 · Answer 1 · answered Dec 05 '20 at 00:18

A mere 4 years late, but I notice this page gets viewed often. Jan Karel Pieterse has a free downloadable VBA macro set that can edit OOXML. Editing elements in an OpenXML file using VBA

It's not a super-elegant method, but it does work. The file to be edited must be closed. The macro unzips the file, allows you to make changes to the XML using VBA string manipulation, then re-zips. The unzip/rezip steps are slow on large files, so its more suitable for batch-processing scenarios where you can let it run without supervision.

Read Office Open XML (OOXML) part Using Excel VBA

1 Answers1

Linked