Hi Charles,
I am currenly opening a Word Document in VB.NET and placing data inside the
document and this works great. Just wanted to know if there is a way to
embed an excell spreadsheet inside a Word Document using vb.net code. This
excell spreadsheet would also have to maintain any calculations designed
inside the spreadsheet.
Yes... Using Word's COM object model, of course
Start by opening Word and inserting an Excel spreadsheet. Play with that until
you know the steps to get what you want. Now start Word's macro recorder
(Toosl/Macro) and record the steps.
Note: It will only record up through inserting the table. Anything you do IN
the table is outside Word's scope, so won't be recorded.
Alt+F11 to open Word's VB Editor, look for the New Macros project. You'll find
the basic code you need to get started and create the Excel object in there.
Take a good look at that, and adapt it to your .NET programming language.
Now comes the tricky part: building on this to add data to the cells, and
maintain it at a later point. In Special Merges section of my website's mail
merge FAQ you'll find an article on merging to a chart. There's a sample file
in that article that shows you how to activate an COM OLE Server object,
assigning it to an object variable of the correct class. The sample code shows
this for MS Graph, but the same principle applies to Excel charts and tables.
Once you have that object (an Excel.Workbook object for a spreadsheet), you
use the EXCEL object model to manipulate the "internals".
The very worst part about doing this with Excel is that you have no way of
releasing the object once you're finished. In the Word document, you'd click
outside the object or press Esc. VBA code can't do this. That means you really
need to create and open the object in its own Excel window, instead of
activating it in-place in the Word document. This means you'll get
screen-flicker; can't be avoided.
Another problem is that you can't set the number of rows and columns that will
be displayed in the Word document. There simply is no automation interface for
this.
So, what you might want to consider, at least for the creation part, is to
create the Excel workbook first in Excel and save it to disk. Then create a
LINK field to that object in the Word document. That will display all the rows
and columns, by default. You can then unlink the field to embed the object.
In order to see the LINK field and its syntax, use Insert/Object/From File in
the Word UI. And you should probably look at the article I wrote on using
Tables in Word on the MSDN site, very last section.
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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
