Microsoft Word - Using an embedded Excel Worksheet in Word VBA

Asked By Zachary on 28-Apr-12 12:57 PM

I have a worksheet embedded in a word document that I use to store a list of label information. It's very handy, but the annoying part of using it is that it opens the worksheet when I use the form macro.
I'm currently using the following code to activate the excel object and set it to a useable late bound object

shape1.OLEFormat.Activate

Set wb = GetObject(, "Excel.Application")

I'm trying to figure out if there is a way I can either hide the excel window or minimize it so it's out of the way, but still be able to access the information. Is this possible? Thanks -Zack

LIJO PHILIP replied to Zachary on 28-Apr-12 11:33 PM

if opening the workbook read only ALLWAYS means the workbook.open macro should not run then try with the following line as the first line in that macro.

else you might want to "ask the user" if the macro shud be run if readonly = yes

If ActiveWorkbook.ReadOnly Then exit Sub

If it is only the data that you want, then this article describes how to extract data from a workbook without opening it (and therefore without the macro issue)

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

OR

add in the check to the auto_open to see if it's readOnly and that's working great!

Somesh Yadav replied to Zachary on 30-Apr-12 12:53 AM

this can't be done the way you want.

Editing the embedded worksheet with VBA from word is not so difficult
When you edit an embedded object MSWord will start a new instance of
excel.. and finding it is easy (IF excel wasn't running beforehand!!!)
see code below

But you propose to use Excel to call Word to call a new Excel:
Thus
Excel1
Word
Excel2

It is impossible to tell which instance of excel will be
be returned by the GetObject function. (see MSDN)

'RUN THIS FROM WORD VBA
Sub EditEmbeddedWkb()
Dim wdOle As OLEFormat
Dim xlApp As Object
Dim xlWkb As Object

'Get he inline worksheet
Set wdOle = ActiveDocument.InlineShapes(1).OLEFormat
'Start editing (this will start an excel instance)
wdOle.DoVerb 'wdOLEVerbOpen will edit in a fully visible window

'Now get the excel application.object
'will work only if excel isn't running yet.
Set xlApp = GetObject(, "Excel.Application")
Set xlWkb = xlApp.workbooks(1)
'Do your stuff
xlWkb.worksheets(1).Cells(3, 2) = "Edited from MSword vba at " & Now()

'Close the references to excel objects
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Zachary replied to Somesh Yadav on 30-Apr-12 05:16 PM

I'm not sure I've been clear.
What I have is a MS Word document that generates case labels for a list of products. Since the labels have different title text and barcode information for each part number, I have embedded an excel spreadsheet in the MS Word document that contains all the information for each part number.
The user selects the part number and enters a few bits of information. The Word VBA code then retrieves the additional data from the embedded worksheet and uses that data to populate the label. I don't have any problem getting the application into the object or with getting any data.

The thing I don't like is that when the macro activates the excel spreadsheet to retrieve the data, the spreadsheet opens visibly on the screen. I tried to use

wb.Application.WindowState=xlminimized

where wb is the name of the object that currently contains the spreadsheet, but it tells me that the application does not support that object or property.
Does this make more sense?
-Zack