Open Excel file for reading with VBA without display

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt filename with path gt   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  quot C  MyData xlsx quot     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  quot C  MyData xlsx quot   Worksheets  quot MySheet quot     your codes here sh Parent Close SaveChanges  False  Closes the associated workbook

User · Answer

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    amp  objFile Path  amp    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Open Excel file for reading with VBA without display

Examples related to excel

Examples related to vba

Examples related to file-io