Open Excel file for reading with VBA without displ

问题:

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?

回答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

回答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.

回答3:

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. :)

回答4:

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

回答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.

回答6:

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...

回答7:

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.

回答8:

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.

回答9:

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.