Creating Excel Spreadsheet in Word with VBA

[melon]

Kind of a stupid question. Is it possible to create/embed an excel
spreadsheet into a word document with VBA?

I can just create a regular table and then copy and paste to Excel,
but I want to know if there is any better option.

 

[Jay Freedman]

Kind of a stupid question. Is it possible to create/embed an excel
spreadsheet into a word document with VBA?

I can just create a regular table and then copy and paste to Excel,
but I want to know if there is any better option.

You can use VBA syntax like this:

Selection.InlineShapes.AddOLEObject FileName:="C:\SomeFolder\book1.xls"

There are lots of variations on this.

 

[melon]

I got an error with this (type mismatch)

objWord.ActiveDocument.Bookmarks("Required").Range.InlineShapes.AddOLEControl
ClassType:="Excel.Sheet"

What cause that?

 

[melon]

I got an error with this (type mismatch)

objWord.ActiveDocument.Bookmarks("Required").Range.InlineShapes.AddOLEControl
ClassType:="Excel.Sheet"

What cause that?

I don't think the syntax is incorrect, since if I change the ClassType
to something like "Forms.ComboBox.1" then it works.

 

[Jay Freedman]

I don't think the syntax is incorrect, since if I change the ClassType
to something like "Forms.ComboBox.1" then it works.

I don't know why that doesn't work -- the documentation says it should be OK --
but I get the same error.

Instead of the AddOLEControl method, use the AddOLEObject method (which is what
I showed in my first response). I found that does work.

 

[Peter Jamieson]

FWIW I think the immediate reason is that the Excel.Sheet is not defined to
be a "Control" (if you look in the registry for Forms.ComboBox.1 and find
the entry for its CLSID, it has a Key called "Control" under it. If you
experimentally add one under the CLSID for "Excel.Sheet", restart Word and
try again, Word will go to the next stage and fail with a memoory-related
message, presumably because the object does not actually implement whatever
interfaces it needs to be a "Control".

 

[Jay Freedman]

I think you're correct, but the MS help (and MSDN) don't qualify the
ClassType values that way. If you go to the topic on AddOLEControl and then
follow the link in that topic to the one on OLE Programmatic Identifiers
(http://msdn2.microsoft.com/en-us/library/aa200268(office.10).aspx ),
Excel.Sheet appears to be a valid choice.

 

[Peter Jamieson]

Agreed.