Making an Excel file invisible

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,304
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I have the following code. In the Else part of the code, after the wb.Activate line, what code can I place here that will assign wb.Name to the
objExcelApp object ao that when the line .Visible = False kicks in, it will make the already opened MS Excel file invisible.

Code:
    Dim strExcelFile As String
    Dim wb As Workbook
    Set objExcelApp = GetObject(, "Excel.Application")
    
    If objExcelApp Is Nothing Then
        Set objExcelApp = CreateObject("Excel.Application")
        objExcelApp.Workbooks.Open FileName:=strExcelFile
    Else
        For Each wb In objExcelApp.Workbooks
            If wb.Name = Right(strExcelFile, Len(strExcelFile) - InStrRev(strExcelFile, "\")) Then
                wb.Activate                
                Exit For
            End If
        Next wb
    End If

    With objExcelApp
        .Visible = False
        'other code
    End With
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi,

Don't use GetObject - just go straight to CreateObject. Then you will have your "own" instance of Excel and it will be invisible by default. Since you are opening the workbook there is no need to activate it (you already have a reference to it and that is all you need):


Code:
Set objExcelApp = CreateObject("Excel.Application")
set wb = objExcelApp.Workbooks.Open FileName:=strExcelFile
 
Upvote 0
The reason I have used GetObject is that the Excel could potentially be already open.
 
Upvote 0
It's better to not interfere with whatever the (open) Excel is doing - especially if a user is using it. Create your own instance so that your code uses it's own instance of Excel. Then you run into fewer problems.
 
Upvote 0
If I use

Code:
    Set objExcelApp = CreateObject("Excel.Application")
    
    If objExcelApp Is Nothing Then
        Set objExcelApp = CreateObject("Excel.Application")
        objExcelApp.Workbooks.Open FileName:=strExcelFile
    Else
        For Each wb1 In objExcelApp.Workbooks
            If wb1.Name = Right(strExcelFile, Len(strExcelFile) - InStrRev(strExcelFile, "\")) Then
                Set wb2 = objExcelApp.Workbooks(wb1)
                Exit For
            End If
        Next wb1
    End If

and the MS Excel file that I want to interrogate is already open, it bypasses the Else part of the If...Then...Else section of code. From what you are saying, how would I amend the code for it to work both if the MS Excel file is closed or already open?
 
Upvote 0
I would avoid at all costs trying to work with Excel files that may or may not be open. This introduces many messy unknowns - what is the state of the file, is it saved or unsaved, is someone actually working with the file or not, are current changes saved or unsaved, will it need to stay open or be closed (and indeed, if it is open and in use why would you want to make it invisible).

However, you may be able to work around these issues (assuming you only want to read from the file) by opening it read-only:
Code:
Set objExcelApp = CreateObject("Excel.Application")
set wb = objExcelApp.Workbooks.Open FileName:=strExcelFile, ReadOnly:=True

Also, do set your workbook reference as I have shown. There is no need to loop over open workbooks for that.
 
Upvote 0
I have the first function in a MS Access module, that opens a new file that sends data and creates a graph in the first sheet. This function keeps the file open.

The second function does the same thing to the SAME file but on different data and hence a different graph on a different sheet.

Hence I wanted to deal with a specific file that would initially be opened using CreateObject but when the second function kicked in I thought I would keep the file open rather than closing it and re-opening it in the second function. Hence using GetObject and CreateObject in the code I have given in my posts.

I agree with the point that you are making as it is messy as you have said. I initially use a function that closes all open workbooks before initiating the main code and opening the file in question.

I could of course close the file in the first function and re-open it in the second function. This will ensure that I don't have the unknowns which I keep getting. Aaaargh. This way I need only use CreateObject as you have suggested and hence get rid of the headaches.

This is a pivotal moment in my career as what you have been saying is the direction that my mind has been going as I do a lot of stuff on Automation between Access and Excel and keep getting these issues.

As you said I don't have to loop thru the workbooks as only one workbook will be open at any one time. I guess there will be times when more than 1 workbook will need to be open but I can deal with that as and when it happens. Good to know that I can open a workbook Read Only.
 
Upvote 0
Hi,
Okay. I would actually prefer to close and re-open as the safest option, but if both functions are working in close connection with each other you can open the workbook first and send it as an argument to the functions:

Code:
[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] objExcelApp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    [COLOR="Navy"]Set[/COLOR] objExcelApp = CreateObject("Excel.Application")
    [COLOR="Navy"]Set[/COLOR] wb = objExcelApp.Workbooks.Open(Filename:=strExcelFile, ReadOnly:=True)
    
    [COLOR="Navy"]Call[/COLOR] Function_One(wb)
    [COLOR="Navy"]Call[/COLOR] Function_Two(wb)
    
    wb.Close True
    [COLOR="Navy"]Set[/COLOR] wb = [COLOR="Navy"]Nothing[/COLOR]

My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] wb [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    wb.Close False [COLOR="SeaGreen"]'//Don't save if we have reached an error condition[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objExcelApp [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    objExcelApp.Quit
    [COLOR="Navy"]Set[/COLOR] objExcelApp = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

ErrHandler:
[COLOR="Navy"]Resume[/COLOR] My_Exit

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="SeaGreen"]'---------------------------------------[/COLOR]
[COLOR="Navy"]Function[/COLOR] Function_One(ByRef wb [COLOR="Navy"]As[/COLOR] Object)
    [COLOR="SeaGreen"]'// Code[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="SeaGreen"]'---------------------------------------[/COLOR]
[COLOR="Navy"]Function[/COLOR] Function_Two(ByRef wb [COLOR="Navy"]As[/COLOR] Object)
    [COLOR="SeaGreen"]'// Code[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Some things added here are making sure that the App get's closed properly even if there is an error (we could go one step further and have the functions return a value to indicate success or error as well, such as True/False or 0/1 return values). The app get closed by falling through to the "Exit" block. But we close and set the wb to nothing first, since that's tested in the "Exit" block.

For what it's worth, I don't consider GetObject() reliable, which is another reason I just prefer not to use it. It seems to not work at times in ways that I cannot figure out.

If you have multiple workbooks *always* keep track of them with reference variables at the time you open them:

Code:
Dim wb1 as workbook
Dim wb2 as workbook

Set wb1 = Workbooks.Open("xxx")
Set wb2 = Workbooks.Open("yyy")

There might be one or two cases where you have to set the reference after you open it, but I think that is relatively rare (maybe when you add a workbook that set keyword doesn't work in the same statement).
 
Last edited:
Upvote 0
Many thanks for that, very useful. Hopefully will keep me out of mischief.

Am I right in saying that

Code:
Set objExcelApp = New Excel.Application

is the same as

Code:
Set objExcelApp = CreateObject("Excel.Application")

Also should a worksheet be declared as

Code:
Dim objExcelSheet As Excel.Worksheet

or
Code:
Dim objExcelSheet As Object

My guess is the latter.
 
Upvote 0
CreateObject() is the same as New ...

As far as using Object vs. explicit datatypes, the difference is that between "Early Binding" and "Late Binding". For early binding, you use explicit data types, and you are allowed to use the New keyword:

Code:
Dim XL as Excel.Application
Dim wb As Excel.Workbook

Set XL = New Excel.Application
Set wb = XL.Workbooks.Open("Book1.xlsx")

But for early binding to work you must *set a reference to the Excel Object Library*. This introduces the inconvenience that if a user changes their Office version, the reference can be broken and the code will crash.

For "Late Binding" you do *not* declare explicit data types:
Code:
Dim XL as Object
Dim wb As Object

Set XL = CreateObject("Excel.Application")
Set wb = XL.Workbooks.Open("Book1.xlsx")

This code is more robust because it will work even if the user gets a new office suite.

I usually write code with early binding, since it has the convenience of intellisense while writing code, and it is easier to debug since the compiler will check syntax. I will then convert to late binding when the coding is complete so that I don't need to worry about which version of Office is being referenced. Because I usually convert to late binding, I also usually use CreateObject() for both early and late binding - then the only thing I need to change to convert to late binding is my dim statements (and unchecking the reference to Excel).

As a secondary point, late bound code can be posted here for others without needing to explain how to set references... ;)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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