Open Excel file for reading with VBA without display
Solution 1
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
Solution 2
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.
Solution 3
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
Solution 4
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
Solution 5
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.
Related videos on Youtube
notnot
Updated on July 05, 2022Comments
-
notnot almost 2 years
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?
-
johny why over 2 years@MunimRashid's answer is best. Should have been selected as answer.
-
-
David about 15 yearsJust 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).
-
vefthym over 11 years@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.
-
vefthym over 11 years@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.
-
sevenkul about 9 yearsIt stops the flashing in the taskbar but causes the flashing in the cursor. Even Application.Cursor doesn't help (in Office 2010 x64).
-
sevenkul about 9 yearsThanks, it worked when I use like this:
ThisWorkbook.Activate: ActiveWindow.Visible = False: Application.ScreenUpdating = False
ALL OTHER CODE HEREApplication.ScreenUpdating = True: ThisWorkbook.Activate: ActiveWindow.Visible = True
Neither taskbar nor cursor flickers. -
Winand over 8 yearsWhy do you need actions with
ActiveWindow
? I think just hiding workbook window after it's been opened has the same effect -
Nigel Heffernan over 8 yearsThe 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.
-
Nigel Heffernan over 8 yearsI 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.
-
Patrick McDonald over 8 years@Nile They all sound like great ideas
-
mauek unak over 7 yearsThis 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 ?
-
Siyon DP over 6 yearsFolowing @mauek unak remark and for sake of simplicity I think the right answar by far is @pstraton
wkbAny.windows(1).Visible=False
-
lanartri over 4 yearsThat 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 -
pstraton over 4 years@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.
-
pstraton over 4 years@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.
-
RobertW081171 about 4 yearsThis 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
-
vipin Paliwal over 3 yearsWorks fine for me?
-
johny why over 2 years@sevenkul Where is your Workbooks.Open?
-
johny why over 2 yearsIn your example, the book isn't "opened as hidden". It's opened, and then hidden. The open process is still visible.
-
johny why over 2 yearsWow, 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!
-
johny why over 2 yearsThe only visible indicators while opening are, the standard progress bar on bottom right. That can be eliminated with
ScreenUpdating=false
beforeGetObject
. -
Elizabeth over 2 yearsworked like a charm! much appreciated