92

I want to search through existing Excel files with a macro, but I don't want to display those files when they're opened by the code. Is there a way to have them open "in the background", so to speak?

Improve this question
1
  • 3
    @MunimRashid's answer is best. Should have been selected as answer. Commented Sep 3, 2021 at 19:21

10 Answers 10

Reset to default
91

Not sure if you can open them invisibly in the current excel instance

You can open a new instance of excel though, hide it and then open the workbooks

Dim app as New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(fileName)
'
' Do what you have to do
'
book.Close SaveChanges:=False
app.Quit
Set app = Nothing

As others have posted, make sure you clean up after you are finished with any opened workbooks

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

It stops the flashing in the taskbar but causes the flashing in the cursor. Even Application.Cursor doesn't help (in Office 2010 x64).
I strongly recommend that you lock down the app session before opening the target workbook: ` App.AutomationSecurity = msoAutomationSecurityForceDisable ` ` App.EnableEvents = False ` ` App.Calculation = xlCalculationManual ` - And you might consider enumerating the AddIns collection and disabling them, too: slow add-in startups will delay the launch of the app session.
@Nile They all sound like great ideas
This is great piece of code, however, I'd like to copy worksheets to this hidden workbook, and I'm afraid it's not possible, or is it ?
Folowing @mauek unak remark and for sake of simplicity I think the right answar by far is @pstraton wkbAny.windows(1).Visible=False
39

If that suits your needs, I would simply use

Application.ScreenUpdating = False

with the added benefit of accelerating your code, instead of slowing it down by using a second instance of Excel.

Improve this answer

3 Comments

This code will not work, excel has an issue which prevents it from actually doing what you need it to do. It will only stop you seeing the workings of the macro, the actual opening of the second book and the workbook itself will still be shown
Works fine for me?
Doesn't work for me at first, several days after, it works in my machine, after building/deploying, it doesn't work in my colleague's machine :(
25

To open a workbook as hidden in the existing instance of Excel, use following:

    Application.ScreenUpdating = False
    Workbooks.Open Filename:=FilePath, UpdateLinks:=True, ReadOnly:=True
    ActiveWindow.Visible = False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
Improve this answer

2 Comments

Thanks, it worked when I use like this: ThisWorkbook.Activate: ActiveWindow.Visible = False: Application.ScreenUpdating = False ALL OTHER CODE HERE Application.ScreenUpdating = True: ThisWorkbook.Activate: ActiveWindow.Visible = True Neither taskbar nor cursor flickers.
@sevenkul Where is your Workbooks.Open?
25

Using ADO (AnonJr already explained) and utilizing SQL is possibly the best option for fetching data from a closed workbook without opening that in conventional way. Please watch this VIDEO.

OTHERWISE, possibly GetObject(<filename with path>) is the most CONCISE way. Worksheets remain invisible, however will appear in project explorer window in VBE just like any other workbook opened in conventional ways.

Dim wb As Workbook

Set wb = GetObject("C:\MyData.xlsx")  'Worksheets will remain invisible, no new window appears in the screen
' your codes here
wb.Close SaveChanges:=False

If you want to read a particular sheet, need not even define a Workbook variable

Dim sh As Worksheet
Set sh = GetObject("C:\MyData.xlsx").Worksheets("MySheet")
' your codes here
sh.Parent.Close SaveChanges:=False 'Closes the associated workbook
Improve this answer

5 Comments

Wow, BEST ANSWER. This is the only answer which literally opens the workbook as hidden (unlike the answers which claim to, but actually open it visible and hide it after). Superb answer, should be the selected answer!
The only visible indicators while opening are, the standard progress bar on bottom right. That can be eliminated with ScreenUpdating=false before GetObject.
worked like a charm! much appreciated
Very good solution, but if we SaveChanges the Excel becomes always invisible and we have to manually Go to View Menu to turn it visible, how to solve this ?
There is also another problem with the GetObject, if we are using it at a sharedfolder like sharepoint, the first call, all is fine, the second call if we didn't close the file previoulsy, we get an error of automation.
16

A much simpler approach that doesn't involve manipulating active windows:

Dim wb As Workbook
Set wb = Workbooks.Open("workbook.xlsx")
wb.Windows(1).Visible = False

From what I can tell the Windows index on the workbook should always be 1. If anyone knows of any race conditions that would make this untrue please let me know.

Improve this answer

Comments

13

Even though you've got your answer, for those that find this question, it is also possible to open an Excel spreadsheet as a JET data store. Borrowing the connection string from a project I've used it on, it will look kinda like this:

strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes"""
strSQL = "SELECT * FROM [RegistrationList$] ORDER BY DateToRegister DESC"

Note that "RegistrationList" is the name of the tab in the workbook. There are a few tutorials floating around on the web with the particulars of what you can and can't do accessing a sheet this way.

Just thought I'd add. :)

Improve this answer

4 Comments

@ShawnZhang Most of the Excel files we've been reading have been exported in the 2000/2003 format since that's what the sender's program emits. There is a variation of the connection string that we've tested for 2007/2010 files that works as well.
@ShawnZhang More importantly, it seems the tab name (in the square brackets, with the $ at the end) has some funny restrictions that I can't seem to locate at the moment. Ditto for the column headers.
The Jet OLEDB drivers have a memory leak when reading Excel (all versions, and the predecessor ODBC drivers): you're going to get error messages about available memory if you do this more than once in any user session. Ignore those errors at your peril - your application will eventually freeze or crash. Microsoft do not acknowledge that this problem exists, and there is no documentation on it whatsoever.
That also works with ACE/DAO using the syntax SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\somefolder\myfile.xlsx].[sheetName$]; Works with XLS and XLSX witout a glitch
5

The problem with both iDevlop's and Ashok's answers is that the fundamental problem is an Excel design flaw (apparently) in which the Open method fails to respect the Application.ScreenUpdating setting of False. Consequently, setting it to False is of no benefit to this problem.

If Patrick McDonald's solution is too burdensome due to the overhead of starting a second instance of Excel, then the best solution I've found is to minimize the time that the opened workbook is visible by re-activating the original window as quickly as possible:

Dim TempWkBk As Workbook
Dim CurrentWin As Window

Set CurrentWin = ActiveWindow
Set TempWkBk = Workbooks.Open(SomeFilePath)
CurrentWin.Activate      'Allows only a VERY brief flash of the opened workbook
TempWkBk.Windows(1).Visible = False 'Only necessary if you also need to prevent
                                    'the user from manually accessing the opened
                                    'workbook before it is closed.

'Operate on the new workbook, which is not visible to the user, then close it...
Improve this answer

4 Comments

Why do you need actions with ActiveWindow? I think just hiding workbook window after it's been opened has the same effect
@Windand: as I described above, the point is to minimize the time that the newly opened workbook is displayed by re-activating the original window as quickly as possible. Actually hiding the opened workbook is optional depending on your needs.
@Windand: Reactivating an already rendered window image is extremely fast whereas de-allocating a visible window requires the manipulation of system resources, which is undoubtedly slower. Is the difference enough to care about? Don't know.
@pstraton I tried your solution the problem is that the Excel will remain invisible If you make changes and saved them, I mean next time you open the Excel, you will not be able to see the sheet, except if you go manually to Window/UnHide...
3

Open the workbook as hidden and then set it as "saved" so that users are not prompted when they close out.

Dim w As Workbooks

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Set w = Workbooks
    w.Open Filename:="\\server\PriceList.xlsx", UpdateLinks:=False, ReadOnly:=True 'this is the data file were going to be opening
    ActiveWindow.Visible = False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    w.Item(2).Saved = True 'this will suppress the safe prompt for the data file only
End Sub

This is somewhat derivative of the answer posted by Ashok.

By doing it this way though you will not get prompted to save changes back to the Excel file your reading from. This is great if the Excel file your reading from is intended as a data source for validation. For example if the workbook contains product names and price data it can be hidden and you can show an Excel file that represents an invoice with drop downs for product that validates from that price list.

You can then store the price list on a shared location on a network somewhere and make it read-only.

Improve this answer

1 Comment

In your example, the book isn't "opened as hidden". It's opened, and then hidden. The open process is still visible.
1

Open them from a new instance of Excel.

Sub Test()

    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")

    Dim w As Workbook
    Set w = xl.Workbooks.Add()

    MsgBox "Not visible yet..."
    xl.Visible = True

    w.Close False
    Set xl = Nothing

End Sub

You need to remember to clean up after you're done.

Improve this answer

1 Comment

Just to emphasize, you must cleanup after you are done with an invisible instance. If you don't the user might not be able to open excel files by double clicking or launching from other apps unless they logoff or know how to kill processes (because the files get opened in the invisible window).
0

In excel, hide the workbooks, and save them as hidden. When your app loads them they will not be shown.

Edit: upon re-reading, it became clear that these workbooks are not part of your application. Such a solution would be inappropriate for user workbooks.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.