Hiding excel when opening workbook from word

[bradyboyy88]

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

 

[bradyboyy88]

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.

' 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

Your a genius.... Any idea how to do something similar to this from the desktop? For instance, say I am trying to hide that I am using excel. I have stripped away all the gui components of excel and made it responsive so you would never know its excel. However, the desktop has the icon of the excel worksheet. So what I have done is create a bat file which allows me to open the excel file. The only thing I need to figure out is how to hide the excel loading screen from coming up. Any ideas how this could be possible?

 

[Leith Ross]

Hello bradyboy88,

I am not sure you can hide the Excel loading screen while the Excel application is visible because of integrated system security. Perhaps you could load the Excel application in a hidden state and then make it visible afterward.

Paste the code below into a VBA Module. It will load Excel but not make it visible until after it has loaded.

Public Declare Function ShowWindow Lib "User32.dll" (ByVal hWnd As Long, ByVal nCmdShow As Integer) As Long


Sub TestIt2()


    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", vbHide)
	ShowWindow xlApp.hWnd, vbNormalFocus
        
End Sub

 

[bradyboyy88]

Hello bradyboy88,

I am not sure you can hide the Excel loading screen while the Excel application is visible because of integrated system security. Perhaps you could load the Excel application in a hidden state and then make it visible afterward.

Paste the code below into a VBA Module. It will load Excel but not make it visible until after it has loaded.

Public Declare Function ShowWindow Lib "User32.dll" (ByVal hWnd As Long, ByVal nCmdShow As Integer) As Long


Sub TestIt2()


    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", vbHide)
    ShowWindow xlApp.hWnd, vbNormalFocus
        
End Sub

Thanks for the reply. This would require excel to already be open and then run the sub routine to open the excel file you mention. I am trying to make the initial loading/starting screen not show up when the first instance of excel starts. I was thinking there might be some windows object I could run when I execute the excel file via the .bat file. I was open to changing that to whatever could accomplish this task. Il do some more digging around. I need to hide this being excel completely. That is what I am aiming for and hiding the first instance of the workbook opening when no other ones are open is turning out to be harder than I thought!

 

[Teeroy]

You could try opening excel using a VBscript file. The default mode is not visible so you don't get the loading screen. The only issue I can think of is that the excel application closes when the VBScript file goes out of scope.

 

[bradyboyy88]

You could try opening excel using a VBscript file. The default mode is not visible so you don't get the loading screen. The only issue I can think of is that the excel application closes when the VBScript file goes out of scope.

When you say out of scope, what do you mean? I am only familiar with scope relating to public, private, inheritance, etc. I have never written a VBScript but il look into that now.

Thanks!

 

[Teeroy]

The object bound to the excel application loses scope when the script completes and by default the application opened ends when the script ends however a bit of testing found a way around this.

Try the following in a .VBS File:

Set oExcel = CreateObject("Excel.Application")

oExcel.UserControl=1 'this allows excel to remain open once the Script ends

oExcel.workbooks.open("C:\Users\XXXX\Desktop\New Microsoft Excel Worksheet.xlsm") 'This was my Test File

'oExcel.Application.Visible = 1 'If you unremark this then excel becomes visible

Set oExcel = Nothing

The Test File has a WorkBook_Open sub in the ThisWorkbook module to launch a UserForm.

Private Sub Workbook_Open()
UserForm1.Show

End Sub

The userform shows without the excel application becoming visible.

Keep in mind the VBA code is synchronous with the VBS so the script doesn't finish until the VBA code finishes.

You should also close the excel Application either from the Workbook, or the VBS, otherwise an invisible instance remains in memory.

I hope this helps.

 

[bradyboyy88]

The object bound to the excel application loses scope when the script completes and by default the application opened ends when the script ends however a bit of testing found a way around this.

Try the following in a .VBS File:

Set oExcel = CreateObject("Excel.Application")

oExcel.UserControl=1 'this allows excel to remain open once the Script ends

oExcel.workbooks.open("C:\Users\XXXX\Desktop\New Microsoft Excel Worksheet.xlsm") 'This was my Test File

'oExcel.Application.Visible = 1 'If you unremark this then excel becomes visible

Set oExcel = Nothing

The Test File has a WorkBook_Open sub in the ThisWorkbook module to launch a UserForm.

Private Sub Workbook_Open()
UserForm1.Show

End Sub

The userform shows without the excel application becoming visible.

Keep in mind the VBA code is synchronous with the VBS so the script doesn't finish until the VBA code finishes.

You should also close the excel Application either from the Workbook, or the VBS, otherwise an invisible instance remains in memory.

I hope this helps.

Your code works great! only problem is when it creates the new instance , it has no window frame. So no maximize, hard to move it then, and no minimize icon. My code which I have already posted in a few threads is as follows:

Option Explicit
'Seperate instance variables
Private WithEvents oAppEvents As Application
Private oWb As Workbook




Private Sub Workbook_Open()


'Maintain seperate instance for workbooks from this application
Call SeperateInstances


'Update the Gui and create responsiveness
Call GuiUpgrade


'Icon Update
Call IconUpdate


End Sub


'create seperate instances
Private Sub SeperateInstances()
'Maintain seperate instance for workbooks from this application




 
    'If Application.Workbooks.Count > 1 Then
        
    '    Dim objExcel
    '    Set objExcel = CreateObject("Excel.Application")
    '   objExcel.Visible = True
    '    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\BAUN\Desktop\MMC Rate Review Tool Beta.xlsm")
    '    Me.Close False
        
    'End If












End Sub
Private Sub oAppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    Dim oNewApp As New Application
 
    Wb.Close False
    oNewApp.Workbooks.Add
    oNewApp.Visible = True


End Sub
 
Private Sub oAppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Wb Is Me Then Exit Sub
    Set oWb = Wb
    oWb.ChangeFileAccess xlReadOnly
    Application.OnTime Now, Me.CodeName & ".CloseWB"


End Sub
 
Private Sub CloseWB()
 
    Dim oNewApp As New Application
    oNewApp.Workbooks.Open oWb.FullName
    Call GuiBackToNormal
    oNewApp.Visible = True
    oWb.Close False
     
End Sub




Private Sub GuiUpgrade()
    
    'Excel UI Removal
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    ThisWorkbook.Windows(1).DisplayWorkbookTabs = False
    ThisWorkbook.Windows(1).DisplayHorizontalScrollBar = False
    ThisWorkbook.Windows(1).DisplayGridlines = False
    ThisWorkbook.Windows(1).DisplayHeadings = False
    Application.ScreenUpdating = True


    
    'Update Caption on Window Frame
    ThisWorkbook.Windows(1).Caption = ""
    Application.Caption = "MMC Review Tool Beta"
    
    'Create responsiveness
    
End Sub

I was attempting to strip away the gui which works great. However, that one function I tabbed out did a similar thing when creating a new instance which was remove the window frame for some reason. Its also not running my workbook_open all the way because I made a function which updates the icon and that is not being done (I believe its because its using activewindow32() in there any maybe this method doesnt activate the window since its not showing up?). Not sure why but its probably related

Option Explicit


'Create Icons
Declare Function ExtractIcon32 Lib "shell32.dll" Alias _
"ExtractIconA" (ByVal hInst As Long, _
ByVal lpszExeFileName As String, ByVal nIconIndex As Long) As Long


Declare Function GetActiveWindow32 Lib "user32" _
Alias "GetActiveWindow" () As Integer


Declare Function SendMessage32 Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long


Sub IconUpdate()
Dim myIcoFile As String
Dim NewIco
    
    'Icon Update
    ThisWorkbook.Windows(1).Activate
    myIcoFile = "C:\Users\blah\Desktop\untitled.ico"
    NewIco = ExtractIcon32(0, myIcoFile, 0)
    SendMessage32 GetActiveWindow32(), &H80, 1, NewIco
        
End Sub

oddly enough this vbscript doesnt cause the same problem but does show all the nasty updating stuff in the workbook_open() . Here is the vbscript:

Dim objExcel
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\blah\Desktop\Tool Beta.xlsm")

 

[bradyboyy88]

The object bound to the excel application loses scope when the script completes and by default the application opened ends when the script ends however a bit of testing found a way around this.

Try the following in a .VBS File:

Set oExcel = CreateObject("Excel.Application")

oExcel.UserControl=1 'this allows excel to remain open once the Script ends

oExcel.workbooks.open("C:\Users\XXXX\Desktop\New Microsoft Excel Worksheet.xlsm") 'This was my Test File

'oExcel.Application.Visible = 1 'If you unremark this then excel becomes visible

Set oExcel = Nothing

The Test File has a WorkBook_Open sub in the ThisWorkbook module to launch a UserForm.

Private Sub Workbook_Open()
UserForm1.Show

End Sub

The userform shows without the excel application becoming visible.

Keep in mind the VBA code is synchronous with the VBS so the script doesn't finish until the VBA code finishes.

You should also close the excel Application either from the Workbook, or the VBS, otherwise an invisible instance remains in memory.

I hope this helps.

I have found that if I do not set the ribbon to false until after setting the application to visible in the vbs then the frame does not disappear. Anyway way to change these before being visible so the user cannot see the ribbon for the split second and then disappearing? I followed your code and did:

oExcel.Application.Visible = 1 'If you unremark this then excel becomes visible
oExcel.Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

and then it worked good but as said you can still see the ribbon initially. If I put it before the visible then the frame disappears. I have also updated my vba code to not set it to false in the code since that basically initializes it before application.visible=1 in the vbscript file.

Thanks