How to hide Excel window when activating a Shape object

[Yi]

I have a VB application that processes an Excel file. The
Excel file has a Chart (actually a Workbook) Shape object
embedded in it. The application needs to activate the
Shape object in order to update the data in the worksheet.
When the following code is called, the Excel window will
always popup.
shape.OLEFormat.Verb(Excel.XlOLEVerb.xlVerbOpen)

Can anybody tell me how to hide the Excel window when this
code is called? I can only find two types of verbs - Open
and Primary, and they both display the window.

Or, is there any other way to activate the OLE object
without displaying the Excel window?

Thanks in advance.
Yi

[Bob Kilmer]

Have you tried:

Application.Visible = False
...
shape.OLEFormat.Verb(Excel.XlOLEVerb.xlVerbOpen)
...
Application.Visible = True

--
Bob Kilmer

"Yi" <yc...@ottawamkg.com> wrote in message
news:1264801c3842c$3824a920$a601...@phx.gbl...

[Yi]

Yes, I tried this but it does not help. My codes look like:

excel_app = CreateObject("Excel.Application")
' or excel_app = new Excel.Application
excel_app.Visible = False ' Actually it defaults to False
workbook = excel_app.Workbooks.Open("myfile.xls")
worksheet = workbook.Worksheets("Sheet1")
shape = worksheet.Shapes.Item(1)
....

It seems that the Excel automation server, when invoked
with "Verb" function call, will always try to set the
Excel app's visibility to True. I am not sure if we have
other ways to tell the server at the invoke time that we
do not want to display the window.

Thanks,
Yi

>-----Original Message-----
>Have you tried:
>
>Application.Visible = False

>....
>shape.OLEFormat.Verb(Excel.XlOLEVerb.xlVerbOpen)
>....

>Application.Visible = True
>
>--
>Bob Kilmer
>
>
>"Yi" <yc...@ottawamkg.com> wrote in message
>news:1264801c3842c$3824a920$a601...@phx.gbl...
>> I have a VB application that processes an Excel file.
The
>> Excel file has a Chart (actually a Workbook) Shape
object
>> embedded in it. The application needs to activate the
>> Shape object in order to update the data in the
worksheet.
>> When the following code is called, the Excel window will
>> always popup.
>> shape.OLEFormat.Verb(Excel.XlOLEVerb.xlVerbOpen)
>>
>> Can anybody tell me how to hide the Excel window when
this
>> code is called? I can only find two types of verbs -
Open
>> and Primary, and they both display the window.
>>
>> Or, is there any other way to activate the OLE object
>> without displaying the Excel window?
>>
>> Thanks in advance.
>> Yi
>
>

>.
>

[Yi]

Since my second posting has been posted for two weeks
without getting an anwser, I hope this re-posting could
get help from somebody. Thanks in advance.

The following is my second posting:

Yes, I tried this but it does not help. My codes look like:

excel_app = CreateObject("Excel.Application")
' or excel_app = new Excel.Application
excel_app.Visible = False ' Actually it defaults to False
workbook = excel_app.Workbooks.Open("myfile.xls")
worksheet = workbook.Worksheets("Sheet1")
shape = worksheet.Shapes.Item(1)

.....

[cdavis]

Have you tried using ScreenUpdating, such as
application.ScreenUpdating = false

>-----Original Message-----
>Since my second posting has been posted for two weeks
>without getting an anwser, I hope this re-posting could
>get help from somebody. Thanks in advance.
>
>The following is my second posting:
>
>Yes, I tried this but it does not help. My codes look
like:
>
>excel_app = CreateObject("Excel.Application")
>' or excel_app = new Excel.Application
>excel_app.Visible = False ' Actually it defaults to False
>workbook = excel_app.Workbooks.Open("myfile.xls")
>worksheet = workbook.Worksheets("Sheet1")
>shape = worksheet.Shapes.Item(1)

>......

>.
>

[Yi]

I tried this and found that it does not help.
ScreenUpdating() may block updating screen display but
does not turn off the window.

I know that in Word automation there is a way to do this.
The code is as: shape_in_word.OLEFormat.DoVerb
(word.WdOLEVerb.wdOLEVerbHide)

Thanks,
Yi

>.
>

[Peter Huang [MSFT]]

Hi Yi,

I don't believe it's possible to prevent Excel from making itself visible
in this situation.

The workaround is to re-hide the Excel instance.

The side effect of the workaround above is that Excel will briefly appear
on the screen, then immediately disappear by setting the Visible to False.
If you would like to prevent this "flash" of Excel, you can move the Excel
window off the screen before calling the xlVerbOpen, and then later restore
the previous position before quitting the instance.

Here is a modified version of your code that shows how:

Private Sub Command1_Click()
Dim exApp As Excel.Application
Set exApp = New Application
Dim WS As Worksheet
Dim Wk As Workbook
Dim exLeft As Double
exApp.Visible = False ' Actually it defaults to False
Set Wk = exApp.Workbooks.Open("c:\myfile.xls")
Set WS = Wk.Worksheets("Sheet1")
exApp.ScreenUpdating = False
exLeft = exApp.Left
exApp.Left = -10000
WS.Shapes(1).OLEFormat.Verb (Excel.XlOLEVerb.xlVerbOpen)
exApp.Visible = False
exApp.Left = exLeft
Wk.Close SaveChanges:=False
exApp.Quit
Set exApp = Nothing
End Sub

Hope this helps.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
>Content-Class: urn:content-classes:message
>From: "Yi" <yc...@ottawamkg.com>
>Sender: "Yi" <yc...@ottawamkg.com>
>References: <0b7201c38c22$2f20eed0$a001...@phx.gbl>
<045e01c38c45$e98eae60$a401...@phx.gbl>
>Subject: How to hide Excel window when activating a Shape object
>Date: Tue, 7 Oct 2003 05:41:32 -0700
>Lines: 86
>Message-ID: <0f4601c38cd0$55fa7770$a301...@phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcOM0FX6VtGVkq6uSQGxU6wFAwMlmg==
>Newsgroups: microsoft.public.excel.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:421862
>NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
>X-Tomcat-NG: microsoft.public.excel.programming

[Yi]

Thanks, Peter.

Your solution is very good. It almost perfect except the
flash of the Excel application icon at the bottom of the
window - if I have five Chart objects in one file, it
would flash five times during processing of this file.

I have found another way to resolve this problem - 1. for
each shape object, call its Copy() method; 2. open a Word
document object; 3. call Paste to the Word application; 4.
find the shape object in the document and
call "OLEFormat.DoVerb(Word.WdOLEVerb.wdOLEVerbHide)" to
this object; 5. process the object (it is an Excel
Workbook); 6. Copy and Paste back to the shape object in
Excel file.

This solution is good in that there is no Excel window
flashing at all. But it is slower.

Thanks again for your excellent solution.

Best regards,
Yi

>.
>