How do you insert a self-generating (random numbers) spreadsheet into a Word document so that the document has different numbers each time it is opened?
eg Billy has (random number) marbles and Tom has (random number) marbles. How many do they have all together?

I would like the spreadsheet to be invisible as well and would rather not link the spreadsheet to the word doc.



Help please!!!!!

You can start with this macro for the sample document.

Sub UpdateEmbeddedXLSX()
Dim workbook As InlineShape
For Each workbook In ActiveDocument.InlineShapes
With workbook
If .Type = wdInlineShapeEmbeddedOLEObject Then
''# Excel.Sheet.12 for Excel 2007
If .OLEFormat.ClassType = "Excel.Sheet.12" Then
''# Open Object as spreadsheet
.OLEFormat.DoVerb wdOLEVerbPrimary
''# If you want, you can also do any updates here like
.OLEFormat.Object.ActiveSheet.Cells(2, 2).Value = ".1"
''# Nasty - but it works - SendKeys
SendKeys "{ESC}", True
End If
End If
End With
Next
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub


If you want the macro to run at opening the document, you have to do this settings
To make an existing macro execute whenever you open a document in Microsoft Word, rename it "AutoOpen" by clicking "View," "Macro," "View Macros ...," then choosing the existing macro and clicking "Edit." In the resulting screen, edit the two top lines in the VBA code window to replace the current macro name with "AutoOpen."

Attached Files

New Document Microsoft Office Word.docx (16.9 KB, 9 views)

Sorry, the other macro has some errors, this works:

Private Sub CommandButton1_Click()

Dim workbook As InlineShape
For Each workbook In ActiveDocument.InlineShapes
Application.ScreenUpdating = False
With workbook

On Error Resume Next

If .Type = wdInlineShapeEmbeddedOLEObject Then
''# Excel.Sheet.12 for Excel 2007
If .OLEFormat.ClassType = "Excel.Sheet.12" Then
''# Open Object as spreadsheet
.OLEFormat.DoVerb wdOLEVerbPrimary
''# If you want, you can also do any updates here like
.OLEFormat.Object.ActiveSheet.Cells(2, 2).Value = ".1"
''# Nasty - but it works - SendKeys
SendKeys "{ESC}", True
.OLEFormat.Object.Close

End If
End If
End With
Next
Application.ScreenUpdating = True
End Sub