Hiding excel when opening workbook from word

B

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
Hi,

I was using some code I found online and was wondering if it is possible to open an excel workbook without it ever becoming visible to the screen at all times when it is open. The current code I am using is below. I have tried oXL.visible= False but it still shows up on the screen and application.screenupdating = false also does not seem to help.

Thanks

sub Whatever()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String






'specify the workbook to work on
WorkbookToWorkOn = "C:\Users\coolguy\testfile.xlsx"


'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")


If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If




On Error GoTo Err_Handler




'Open the workbook


Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn, UpdateLinks:=True)


'Random code I am using is going right here



'Process each of the spreadsheets in the workbook
oXL.DisplayAlerts = False
oWB.Save
oXL.DisplayAlerts = True




If ExcelWasNotRunning Then
oXL.Quit
End If


'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing


'quit
Exit Sub


Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
You could try set the Window visible property to False rather than the application.
 
Upvote 0
Where are you putting this line of code?

Code: 
oXL.Visible = [COLOR=#0000ff]False[/COLOR]


This small alteration works fine for me (Using Excel & Word 2010):
Code: 
[COLOR=#0000ff]On Error Resume Next[/COLOR]
[COLOR=#0000ff]Set[/COLOR] oXL = GetObject(, "Excel.Application")
[COLOR=#0000ff]On Error GoTo 0[/COLOR]


[COLOR=#0000ff]If [/COLOR]oXL[COLOR=#0000ff] Is Nothing Then[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] oXL = CreateObject("Excel.Application")
[COLOR=#0000ff]End If[/COLOR]
oXL.Visible =[COLOR=#0000ff] False[/COLOR]
 
Last edited:
Upvote 0
mrmmickle1 said:
Where are you putting this line of code?

Code: 
oXL.Visible = [COLOR=#0000ff]False[/COLOR]


This small alteration works fine for me (Using Excel & Word 2010):
Code: 
[COLOR=#0000ff]On Error Resume Next[/COLOR]
[COLOR=#0000ff]Set[/COLOR] oXL = GetObject(, "Excel.Application")
[COLOR=#0000ff]On Error GoTo 0[/COLOR]


[COLOR=#0000ff]If [/COLOR]oXL[COLOR=#0000ff] Is Nothing Then[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] oXL = CreateObject("Excel.Application")
[COLOR=#0000ff]End If[/COLOR]
oXL.Visible =[COLOR=#0000ff] False[/COLOR]
Click to expand...
That is where I have it, but if you minimize your word document and run the macro you will see the excel file pops up for a split second but does not have the application window.
 
Upvote 0
Teeroy said:
You could try set the Window visible property to False rather than the application.
Click to expand...

So how would i go about doing that?

If I do activewindow.visible = false even after the excel application initialization then it makes the word document go invisible. Is there a way to target the excel window while its loading? Or is it not possible for the excel workbook to hide completely from open to close given these commands go in order 1 by 1 hence after setting the new workbook to open it has to load first to the screen and then you can hide it?
 
Upvote 0
I did some testing across various versions of excel and the response varied (probably with the MDI to SDI concept change in 2013 but I'm not sure) so I don't think that setting the window visible state will work for you.
 
Upvote 0
Teeroy said:
I did some testing across various versions of excel and the response varied (probably with the MDI to SDI concept change in 2013 but I'm not sure) so I don't think that setting the window visible state will work for you.
Click to expand...

I am using 2013 if that helps. Wish I could figure this out!
 
Upvote 0
Hello bradyboyy88,

The macro below will return an object reference to the Excel Application. The macro takes 2 arguments: The Full Workbook Path and How to Display the workbook.

Add a new VBA Module to your Word Document and paste the code below into it. There is also an example of calling the macro.

Macro Code
Code: 
' Written:  June 09, 2016
' Author:   Leith Ross
' Summary:  Starts a new instance of Excel and opens the specified workbook.
'           The workbook can be displayed according to the ShowWindow value.
'           If successful then a reference to the Excel Application object
'           is returned. If not then the object value Nothing is returned.

Private Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDireectory As String, ByVal cmdShow As Long) As Long
Private Declare Function FindWindow Lib "User32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal WindowTitle As String) As Long
Private Declare Function GetWindow Lib "User32.dll" (ByVal hWnd As Long, ByVal uCmd As Long) As Long
Private Declare Function GetWindowText Lib "User32.dll" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpTitle As String, ByVal nMaxCount As Long) As Long
Private Declare Function AccessibleObjectFromWindow Lib "oleacc.dll" (ByVal hWnd As Long, ByVal dwObjectId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long
Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
Private Declare Function FindWindowEx Lib "User32.dll" Alias "FindWindowExA" (ByVal hWndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Private Type UUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Function StartExcel(ByVal WkbPath As String, ByVal fShowWindow As Long) As Object

    Dim hDsk    As Long
    Dim hWbk    As Long
    Dim hWnd    As Long
    Dim Path    As Variant
    Dim Paths   As Variant
    Dim ret     As Long
    Dim riid    As UUID
    Dim Title   As String
    Dim WkbName As String
    Dim xlObj   As Object
    
    Const IID_IDispatch      As String = "{00020400-0000-0000-C000-000000000046}"
    Const OBJID_NATIVEOM     As Long = &HFFFFFFF0
    Const GW_HWNDNEXT        As Long = &H2
        
        Paths = Split(Environ("Path"), ";")
        
        For Each Path In Paths
            Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
            If Dir(Path & "Excel.exe") <> "" Then
                WkbName = Right(WkbPath, Len(WkbPath) - InStrRev(WkbPath, "\"))
                WkbPath = Chr(34) & WkbPath & Chr(34)
                
                GoSub GetHwnd
                
                If hWnd = 0 Then
                    ret = ShellExecute(0&, "open", Path & "excel.exe", WkbPath, "", fShowWindow)
                    Delay = Timer + 0.5
                    While Timer < Delay: DoEvents: Wend
                End If
                    
                GoSub GetHwnd
                            
                hDsk = FindWindowEx(hWnd, 0, "XLDESK", vbNullString): If hDsk = 0 Then Exit Function
                hWbk = FindWindowEx(hDsk, 0, "EXCEL7", vbNullString): If hWbk = 0 Then Exit Function
    
                Call IIDFromString(StrPtr(IID_IDispatch), riid)
        
                If AccessibleObjectFromWindow(hWbk, OBJID_NATIVEOM, riid, xlObj) = 0 Then
                    Set StartExcel = xlObj.Application
                    Exit Function
                End If
                
                Exit Function
            End If
        Next Path
        
Exit Function

GetHwnd:
                hWnd = FindWindow(vbNullString, vbNullString)

                Do Until hWnd = 0
                    Title = String(512, Chr$(0))
                    ret = GetWindowText(hWnd, Title, Len(Title))
                    If ret > 0 Then
                        Title = Left(Title, ret)
                        If LCase(Title) Like "*" & LCase(WkbName) & "*" Then
                            Exit Do
                        End If
                    End If
                    hWnd = GetWindow(hWnd, GW_HWNDNEXT)
                Loop
    Return
    
End Function

Example of Using the Macro
Code: 
Sub TestIt()

    Dim xlApp As Object
    
      ' Change the Workbook path to match a workbook on your system
        Set xlApp = StartExcel("C:\Test\VBA Help ver 1.xlsm", vbMinimizedNoFocus)
        MsgBox xlApp.ActiveSheet.Name
        
End Sub
 
Last edited:
Upvote 0
Leith Ross said:
Hello bradyboyy88,

The macro below will return an object reference to the Excel Application. The macro takes 2 arguments: The Full Workbook Path and How to Display the workbook.

Add a new VBA Module to your Word Document and paste the code below into it. There is also an example of calling the macro.

Macro Code
Code: 
' Written:  June 09, 2016
' Author:   Leith Ross
' Summary:  Starts a new instance of Excel and opens the specified workbook.
'           The workbook can be displayed according to the ShowWindow value.
'           If successful then a reference to the Excel Application object
'           is returned. If not then the object value Nothing is returned.

Private Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDireectory As String, ByVal cmdShow As Long) As Long
Private Declare Function FindWindow Lib "User32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal WindowTitle As String) As Long
Private Declare Function GetWindow Lib "User32.dll" (ByVal hWnd As Long, ByVal uCmd As Long) As Long
Private Declare Function GetWindowText Lib "User32.dll" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpTitle As String, ByVal nMaxCount As Long) As Long
Private Declare Function AccessibleObjectFromWindow Lib "oleacc.dll" (ByVal hWnd As Long, ByVal dwObjectId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long
Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
Private Declare Function FindWindowEx Lib "User32.dll" Alias "FindWindowExA" (ByVal hWndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Private Type UUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Function StartExcel(ByVal WkbPath As String, ByVal fShowWindow As Long) As Object

    Dim hDsk    As Long
    Dim hWbk    As Long
    Dim hWnd    As Long
    Dim Path    As Variant
    Dim Paths   As Variant
    Dim ret     As Long
    Dim riid    As UUID
    Dim Title   As String
    Dim WkbName As String
    Dim xlObj   As Object
    
    Const IID_IDispatch      As String = "{00020400-0000-0000-C000-000000000046}"
    Const OBJID_NATIVEOM     As Long = &HFFFFFFF0
    Const GW_HWNDNEXT        As Long = &H2
        
        Paths = Split(Environ("Path"), ";")
        
        For Each Path In Paths
            Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
            If Dir(Path & "Excel.exe") <> "" Then
                WkbName = Right(WkbPath, Len(WkbPath) - InStrRev(WkbPath, "\"))
                WkbPath = Chr(34) & WkbPath & Chr(34)
                
                GoSub GetHwnd
                
                If hWnd = 0 Then
                    ret = ShellExecute(0&, "open", Path & "excel.exe", WkbPath, "", fShowWindow)
                    Delay = Timer + 0.5
                    While Timer < Delay: DoEvents: Wend
                End If
                    
                GoSub GetHwnd
                            
                hDsk = FindWindowEx(hWnd, 0, "XLDESK", vbNullString): If hDsk = 0 Then Exit Function
                hWbk = FindWindowEx(hDsk, 0, "EXCEL7", vbNullString): If hWbk = 0 Then Exit Function
    
                Call IIDFromString(StrPtr(IID_IDispatch), riid)
        
                If AccessibleObjectFromWindow(hWbk, OBJID_NATIVEOM, riid, xlObj) = 0 Then
                    Set StartExcel = xlObj.Application
                    Exit Function
                End If
                
                Exit Function
            End If
        Next Path
        
Exit Function

GetHwnd:
                hWnd = FindWindow(vbNullString, vbNullString)

                Do Until hWnd = 0
                    Title = String(512, Chr$(0))
                    ret = GetWindowText(hWnd, Title, Len(Title))
                    If ret > 0 Then
                        Title = Left(Title, ret)
                        If LCase(Title) Like "*" & LCase(WkbName) & "*" Then
                            Exit Do
                        End If
                    End If
                    hWnd = GetWindow(hWnd, GW_HWNDNEXT)
                Loop
    Return
    
End Function

Example of Using the Macro
Code: 
Sub TestIt()

    Dim xlApp As Object
    
      ' Change the Workbook path to match a workbook on your system
        Set xlApp = StartExcel("C:\Test\VBA Help ver 1.xlsm", vbMinimizedNoFocus)
        MsgBox xlApp.ActiveSheet.Name
        
End Sub
Click to expand...

Hey I got an error saying delay is not defined so I updated that. I then get an another error which I think may be related to the fact that I need to include links being updated on open. Is there a way to target that such as activeworkbooks.updatelinks ?

Error: object variable or with block not set
 
Upvote 0
Hello bradyboyy88,

I finally got time to rework the macro and test it. This version will allow to you to open the workbook with Links, Read Only, etc. I made sure to declare "Delay" in this version.

Replace the old macro code with the code below. The macro name and usage are the same.

Code: 
' Thread:   http://www.mrexcel.com/forum/excel-questions/946142-hiding-excel-when-opening-workbook-word.html
' Written:  June 09, 2016
' Author:   Leith Ross
' Summary:  Opens the specified workbook in a new instance of Excel if the
'           workbook is not currently running in Excel.
'           The workbook can be displayed according to the ShowWindow value.
'           If successful then a reference to the Excel Application object
'           is returned. If not then the object value Nothing is returned.

Private Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDireectory As String, ByVal cmdShow As Long) As Long
Private Declare Function FindWindow Lib "User32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal WindowTitle As String) As Long
Private Declare Function GetWindow Lib "User32.dll" (ByVal hWnd As Long, ByVal uCmd As Long) As Long
Private Declare Function GetWindowText Lib "User32.dll" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpTitle As String, ByVal nMaxCount As Long) As Long
Private Declare Function AccessibleObjectFromWindow Lib "oleacc.dll" (ByVal hWnd As Long, ByVal dwObjectId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long
Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
Private Declare Function FindWindowEx Lib "User32.dll" Alias "FindWindowExA" (ByVal hWndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Private Type UUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Function StartExcel(ByVal WkbPath As String, ByVal fShowWindow As Long) As Object

    Dim Delay   As Single
    Dim hDsk    As Long
    Dim hWbk    As Long
    Dim hWnd    As Long
    Dim Path    As Variant
    Dim Paths   As Variant
    Dim ret     As Long
    Dim riid    As UUID
    Dim Running As Boolean
    Dim Title   As String
    Dim WkbName As String
    Dim xlObj   As Object
    
    Const IID_IDispatch      As String = "{00020400-0000-0000-C000-000000000046}"
    Const OBJID_NATIVEOM     As Long = &HFFFFFFF0
    Const GW_HWNDNEXT        As Long = &H2
        
                WkbName = Right(WkbPath, Len(WkbPath) - InStrRev(WkbPath, "\"))
                
GetHwnd:        hWnd = FindWindow(vbNullString, vbNullString)
                Do Until hWnd = 0
                    Title = String(512, Chr$(0))
                    ret = GetWindowText(hWnd, Title, Len(Title))
                    If ret > 0 Then
                        Title = Left(Title, ret)
                        If LCase(Title) Like "*" & LCase(WkbName) & "*" Then
                            Exit Do
                        End If
                    End If
                    hWnd = GetWindow(hWnd, GW_HWNDNEXT)
                Loop

              ' Open a new instance of Excel if the workbook is not open.
                If hWnd = 0 Then
                    ret = ShellExecute(0&, "open", "excel.exe", vbNullString, vbNullString, fShowWindow)
                    Delay = Timer + 2
                    While Timer < Delay: DoEvents: Wend
                    hWnd = FindWindow("XLMAIN", vbNullString)
                Else
                    Running = True
                End If
                                        
                hDsk = FindWindowEx(hWnd, 0, "XLDESK", vbNullString)
                If hDsk = 0 Then Exit Function
                
                hWbk = FindWindowEx(hDsk, 0, "EXCEL7", vbNullString)
                If hWbk = 0 Then Exit Function
    
                Call IIDFromString(StrPtr(IID_IDispatch), riid)
        
                If AccessibleObjectFromWindow(hWbk, OBJID_NATIVEOM, riid, xlObj) = 0 Then
                    Set StartExcel = xlObj.Application.ActiveWorkbook
                  ' Open the workbook in the new instance of Excel.
                    If Not Running Then
                        xlObj.Application.Workbooks.Open WkbPath, True    ' Update Links
                        GoTo GetHwnd
                    End If
                End If
    
End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA attachments auto save help
Replies
0
Views
346
cxm687
C
A
  • Question
Macro for extracting selected text and comment from Word to Excel on Mac
Replies
4
Views
136
aminchicago
A
G
  • Question
Excel VBA Open Word Doc and Save as New Word Doc
Replies
1
Views
126
NdNoviceHlp
NdNoviceHlp
M
  • Question
Share excel file on Sharepoint using VBA
Replies
1
Views
138
mariyarashkova
M
D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
222
Daniza Morla
D

Forum statistics

Threads
1,215,152
Messages
6,123,323
Members
449,094
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top