打开 Excel 文件以使用 VBA 读取而不显示

Open Excel file for reading with VBA without display

提问by notnot

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?

我想用宏搜索现有的 Excel 文件，但我不想在代码打开这些文件时显示这些文件。有没有办法让它们“在后台”打开，可以这么说？

回答by Patrick McDonald

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

不确定是否可以在当前的 excel 实例中隐形打开它们

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

您可以打开一个新的 excel 实例，将其隐藏，然后打开工作簿

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

正如其他人发布的那样，请确保在完成任何打开的工作簿后进行清理

回答by Patrick Honorez

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.

具有加速代码的额外好处，而不是通过使用 Excel 的第二个实例来减慢它的速度。

回答by Ashok

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

要打开隐藏在现有 Excel 实例中的工作簿，请使用以下命令：

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

回答by AnonJr

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:

即使您已经得到答案，对于发现此问题的人，也可以将 Excel 电子表格作为 JET 数据存储打开。从我使用过的项目中借用连接字符串，它看起来有点像这样：

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.

请注意，“RegistrationList”是工作簿中选项卡的名称。网上有一些教程，其中详细说明了您可以和不可以通过这种方式访问​​工作表的操作。

Just thought I'd add. :)

只是想我会补充。:)

回答by Anthony Lusardi

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.

据我所知，工作簿上的 Windows 索引应该始终是1. 如果有人知道任何会使这不真实的比赛条件，请告诉我。

回答by pstraton

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.

iDevlop 和 Ashok 的答案的问题在于，根本问题是 Excel 设计缺陷（显然），其中 Open 方法未能遵守 Application.ScreenUpdating 设置为 False。因此，将其设置为 False 对这个问题没有任何好处。

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:

如果 Patrick McDonald 的解决方案由于启动 Excel 的第二个实例的开销而过于繁重，那么我发现的最佳解决方案是通过尽快重新激活原始窗口来最小化打开的工作簿可见的时间：

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

回答by Pen123

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.

这在某种程度上是 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.

通过这样做，虽然不会提示您将更改保存回您从中读取的 Excel 文件。如果您从中读取的 Excel 文件旨在作为验证的数据源，则这很好。例如，如果工作簿包含产品名称和价格数据，则可以将其隐藏，并且您可以显示代表发票的 Excel 文件，其中包含从该价目表中验证的产品下拉列表。

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

然后，您可以将价目表存储在某个网络上的共享位置，并将其设为只读。

回答by guillermooo

Open them from a new instance of Excel.

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

完成后你需要记住清理。

回答by JohnW

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

在excel中，隐藏工作簿，并将它们保存为隐藏。当您的应用加载它们时，它们将不会显示。

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.

编辑：重新阅读后，很明显这些工作簿不是您的应用程序的一部分。这种解决方案不适用于用户工作簿。