PDA

View Full Version : Write Data to Embedded Excel Worksheet in Word



gmaxey
03-27-2016, 07:08 AM
Greetings Excel Masters,

I am a humble Word VBA dabbler who is stumped by a requirement to write data into an embedded (not linked) Excel worksheet in a Word document. For example, I have a content control titled "Test." I want to write the value of that control into cell "A1" of the embedded sheet.


Sub WriteToSheet()
Dim shp As InlineShape
Dim xls As Excel.Workbook
For Each shp In ActiveDocument.InlineShapes
If shp.Type = wdInlineShapeEmbeddedOLEObject Then
If Left(shp.OLEFormat.ProgID, 5) = "Excel" Then
shp.OLEFormat.Activate
Set xls = shp.OLEFormat.Object
xls.ActiveSheet.Range("A1") = ActiveDocument.SelectContentControlByTitle("Test").Item(1).Range.Text
Set xls = Nothing
SendKeys "{ESC}"
Exit For
End If
End If
Next
Set shp = Nothing
End Sub

Here are the results/problems/questions:

1. When I first create the document, embed the worksheet and run the code it seems to work fine. The result of the CC is inserted in the cell.
2. I can change the cell ID e.g., "A1" to "B1" and run the code again and again it seems to work fine.
3. I save, close and reopen the file. I look at Task Manager Processes and see an instance of Excel is running. However, when I attempt to run the code again, I get the error message shown in the attached image.

Excel is clearly installed as an instance is running and I never opened it to show a dialog.

4. Sometimes, and I can't establish a pattern, I will get at message "Excel is waiting on another application to complete an OLE operation" or something like that.

5. The document has about 6 content controls that I need to write their data to the sheet. It seems that I can write the data once for a new document but the documents will be reopened multiple times, content changed and the new content needs to be written into the embedded sheet. Does anyone know of a reliable way to do this tasks to avoid the errors/messages described?

Thanks!

snb
03-27-2016, 01:19 PM
You may have noticed that after saving the Word document the embedded Excel worksheet has been transformed into a picture.
The connection with Excel has been removed.
So the oleformat doesn't work anymore.

I assume using a linked file would prevent these problems.

gmaxey
03-27-2016, 01:56 PM
snb,

No I didn't notice that. I can create a new document, embed a worksheet, edit it, close it, close and save the file, open the file, and open edit the embed worksheet. What is it in my code that would convert the embedded object as a picture?

snb
03-27-2016, 03:06 PM
Well if I reopen the document (W 2010, XL 2010) containing {Embed Ecel...}, this fieldcode is gone.
Not any code responsible for that.

gmaxey
03-28-2016, 06:08 AM
That doesn't happen here.

snb
03-28-2016, 08:11 AM
In that case I can't reproduce your situation.

You might consider to post your Word document (the embedded excelfile inclueded).