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!
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!