How to hide Excel window when activating a Shape object

  • Thread starter Thread starter Yi
  • Start date Start date
Y

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)
......

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
 
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
 
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.

--------------------
 
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
 
Back
Top