Accessing Excel worksheets embedded in a Word document

[Trisped]

I am trying to change the contents of an Excel worksheet embedded in my
MSWord document. Currently I have to create the Excel worksheet in VBA
and save the function call in a variable of the type InlineShape.
Is there a way to find an already existing worksheet and use that? I
am using Word/Excel 12, so I have access to the xml files that make
them up.

The document xml file states:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <w:document
xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns12="http://schemas.microsoft.com/office/2004/7/core"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"
xmlns:w10="urn:schemas-microsoft-comffice:word"
xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"
xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
- <w:body>
- <w>
- <w:r w:rsidR="00D66D7D">
- <wbject w:dxaOrig="9133" w:dyaOrig="7593">
- <v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75"
oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f"
stroked="f">
<v:stroke joinstyle="miter" />
- <v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />

<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype>
- <v:shape id="_x0000_i1025" type="#_x0000_t75"
style="width:456.75pt;height:379.5pt" ole="">
<v:imagedata r:id="rId6" o:title="" />
</v:shape>
<o:OLEObject Type="Embed" ProgID="Excel.Sheet.8"
ShapeID="_x0000_i1025" DrawAspect="Content" ObjectID="_1229377176"
r:id="rId7" />
</wbject>
</w:r>
</w>
- <w:sectPr w:rsidR="00324566" w:rsidSect="00B03864">
<wgSz w:w="12240" w:h="15840" />
<wgMar w:top="1440" w:right="1440" w:bottom="1440" w:left="1440"
w:header="720" w:footer="720" w:gutter="0" />
<w:cols w:space="720" />
<w:docGrid w:linePitch="360" />
</w:sectPr>
</w:body>
</w:document>
---------------------------------------------------------------------
There is also the content of the "embeddings" folder which has the file
"Microsoft_Office_Excel_97-2003_Worksheet1.xls"

Thanks in advance,

Trisped

 

[Cindy M.]

Hi Trisped,

I am trying to change the contents of an Excel worksheet embedded in my
MSWord document. Currently I have to create the Excel worksheet in VBA
and save the function call in a variable of the type InlineShape.
Is there a way to find an already existing worksheet and use that? I
am using Word/Excel 12, so I have access to the xml files that make
them up.

I don't understand the question, entirely. Let me try restating how I understand
your requirement:

You need to know how, using VBA in Word, to create an embedded Excel worksheet, as
an InlineShape. You also need to populate the worksheet with data?

This is possible. It's also possible (and quite simple, as far as the Word side of
things is concerned) to use an existing workbook.

It would also be possible to substitute a different workbook in Word's XML file,
but not by doing it over the document xml that you show. Instead, you'd have to
"unzip" the compressed docx file, open the /word/embeddings folder within that and
substitute the other workbook for an existing workbook.

Or create the folder, put the workbook into it, create the necessary XML code in
the document (the part you show), plus the required entries in other XML files in
the zipped folders (relationships).

Since there's no integrated zipped file handling in VBA, that would require a
third-party library and it would have to be done on a closed document. So if you
want to use VBA and create the embedded workbook "in-place", the object model would
be the better approach.

My personal preference is to insert an existing Excel workbook as an object. This
is the only way to control the number of columns and rows that are displayed. You
can record the steps in a macro to get the basic syntax.

The document xml file states:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <w:document
xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns12="http://schemas.microsoft.com/office/2004/7/core"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"
xmlns:w10="urn:schemas-microsoft-comffice:word"
xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"
xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
- <w:body>
- <w>
- <w:r w:rsidR="00D66D7D">
- <wbject w:dxaOrig="9133" w:dyaOrig="7593">
- <v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75"
oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f"
stroked="f">
<v:stroke joinstyle="miter" />
- <v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0" />
<v:f eqn="sum @0 1 0" />
<v:f eqn="sum 0 0 @1" />
<v:f eqn="prod @2 1 2" />
<v:f eqn="prod @3 21600 pixelWidth" />
<v:f eqn="prod @3 21600 pixelHeight" />
<v:f eqn="sum @0 0 1" />
<v:f eqn="prod @6 1 2" />
<v:f eqn="prod @7 21600 pixelWidth" />
<v:f eqn="sum @8 21600 0" />
<v:f eqn="prod @7 21600 pixelHeight" />
<v:f eqn="sum @10 21600 0" />
</v:formulas>
<vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" />

<o:lock v:ext="edit" aspectratio="t" />
</v:shapetype>
- <v:shape id="_x0000_i1025" type="#_x0000_t75"
style="width:456.75pt;height:379.5pt" ole="">
<v:imagedata r:id="rId6" o:title="" />
</v:shape>
<o:OLEObject Type="Embed" ProgID="Excel.Sheet.8"
ShapeID="_x0000_i1025" DrawAspect="Content" ObjectID="_1229377176"
r:id="rId7" />
</wbject>
</w:r>
</w>
- <w:sectPr w:rsidR="00324566" w:rsidSect="00B03864">
<wgSz w:w="12240" w:h="15840" />
<wgMar w:top="1440" w:right="1440" w:bottom="1440" w:left="1440"
w:header="720" w:footer="720" w:gutter="0" />
<w:cols w:space="720" />
<w:docGrid w:linePitch="360" />
</w:sectPr>
</w:body>
</w:document>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in
the newsgroup and not by e-mail

 

[Trisped]

Thanks Cindy.

I was just looking for any way to access the spread sheet I had already
embedded into the document and change the contents. Apparently the
major step missing was activating the spread sheet before trying to
access it. (if anyone wants additional info on how to do this please
visit http://visualbasic.about.com/od/learnvba/a/WordExcel01.htm)

But since I have your attention and you seem to know what you are
talking about I have one more question. I need to have multiple spread
sheets in my document. I know how to link to spread sheets embedded
into my document (just open the embedded document and copy+paste
special) but if I do this I can't update the spread sheet. Is there a
way to have multiple instances of an embedded spread sheet in your
document?

 

[Cindy M.]

Hi Trisped,

I was just looking for any way to access the spread sheet I had already
embedded into the document and change the contents. Apparently the
major step missing was activating the spread sheet before trying to
access it. (if anyone wants additional info on how to do this please
visit http://visualbasic.about.com/od/learnvba/a/WordExcel01.htm)

But since I have your attention and you seem to know what you are
talking about I have one more question. I need to have multiple spread
sheets in my document. I know how to link to spread sheets embedded
into my document (just open the embedded document and copy+paste
special) but if I do this I can't update the spread sheet. Is there a
way to have multiple instances of an embedded spread sheet in your
document?

Not really... If you had the spreadsheet saved to disk, then you could
link in the separate sheets/ranges and they'd all come from the same
source.

But this doesn't really work for a spreadsheet that's embedded in the
document. I have tried, on occasion, to copy from one embedded "source";
open another spreadsheet object then paste special with a link. In more
recent versions of Word this will actually work... Until I close the
document. At that point, the link is lost.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail