logo标签列表

使用VBA打开Excel文件以供读取，无需显示。

excelvbafile-io
86

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

我希望能够编写一个宏来搜索多个Excel文件并收集数据，但是当这些文件被宏打开时，我不想看到它们被打开。请问有没有一种方法可以使这些Excel文件“在后台”打开而又不会对用户造成干扰？
1
@MunimRashid的回答是最好的。应该被选为答案。 - johny why
10个回答
89

不确定您是否能在当前的 Excel 实例中以隐形方式打开它们

但是，您可以打开一个新的 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

正如其他人所回复的一样，请确保在完成任何打开的工作簿后进行清理。

它停止了任务栏中的闪烁，但导致光标闪烁。即使是Application.Cursor也无法解决（在Office 2010 x64中）。 - sevenkul
10
我强烈建议在打开目标工作簿之前锁定应用程序会话：App.AutomationSecurity = msoAutomationSecurityForceDisable App.EnableEvents = False App.Calculation = xlCalculationManual
  • 你可能还需要考虑枚举AddIns集合并禁用它们：缓慢的插件启动将延迟应用程序会话的启动时间。
 - Nigel Heffernan
@Nile，它们听起来都是很棒的想法。 - Patrick McDonald
这是一段很棒的代码，但是我想将工作表复制到这个隐藏的工作簿中，我担心这不可能，或者说可以吗？ - mauek unak
1
根据@mauek的评论和为了简单起见，我认为迄今为止正确的答案是@pstraton的wkbAny.windows(1).Visible=False。 - Siyon DP
38

如果那符合你的需求，我会简单地使用：

Application.ScreenUpdating = False

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

2
这段代码不会起作用，因为 Excel 存在一个问题，阻止它实际执行你需要它执行的操作。它只会阻止你看到宏的工作过程，但第二个工作簿和工作簿本身的实际打开仍将显示。 - RobertW081171
1
对我来说运行良好？ - SAL
一开始对我没用，几天后，在我的电脑上工作了，但在构建/部署后，在我同事的电脑上不起作用 :( - user1108069
24

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

    Application.ScreenUpdating = False
    Workbooks.Open Filename:=FilePath, UpdateLinks:=True, ReadOnly:=True
    ActiveWindow.Visible = False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
谢谢，当我像这样使用时它起作用了：ThisWorkbook.Activate: ActiveWindow.Visible = False: Application.ScreenUpdating = False其他所有代码在此处Application.ScreenUpdating = True: ThisWorkbook.Activate: ActiveWindow.Visible = True 任务栏和光标都不会闪烁。 - sevenkul
@sevenkul，你的Workbooks.Open在哪里？ - johny why
19

使用 ADO（AnonJr 已经解释过了）并利用 SQL 可能是从未以常规方式打开的关闭的工作簿中获取数据的最佳选项。 请观看此视频。

否则，可能GetObject(<filename with path>) 是最简洁的方法。 工作表保持不可见，但在 VBE 中的项目资源管理器窗口中将显示，就像以常规方式打开的任何其他工作簿一样。

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
如果您想阅读特定的工作表，甚至无需定义工作簿变量。
Dim sh As Worksheet
Set sh = GetObject("C:\MyData.xlsx").Worksheets("MySheet")
' your codes here
sh.Parent.Close SaveChanges:=False 'Closes the associated workbook
4
哇，最佳答案。这是唯一一个真正将工作簿隐藏起来的答案（与那些声称会这样做但实际上是打开可见并稍后隐藏它的答案不同）。非常棒的答案，应该被选为最佳答案！ - johny why
2
打开时唯一可见的指示器是右下角的标准进度条。在GetObject之前使用ScreenUpdating=false可以消除它。 - johny why
运行得非常好！非常感谢。 - Elizabeth
非常好的解决方案，但如果我们SaveChanges，Excel就变得始终不可见，我们必须手动转到“视图”菜单才能将其显示出来，如何解决这个问题？ - JustGreat
GetObject还有另一个问题，如果我们在共享文件夹（如SharePoint）中使用它，第一次调用时一切正常，但如果我们之前没有关闭文件并进行第二次调用，就会出现自动化错误。 - JustGreat
15

一个更简单的方法，不涉及操作活动窗口：

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

据我所知，工作簿上的Windows索引始终应为1。如果有人知道任何可能导致这不成立的竞态条件，请告诉我。

13

尽管您已经得到了答案，但对于那些看到这个问题的人来说，也可以将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"

请注意，“RegistrationList”是工作簿中选项卡的名称。网络上有一些教程介绍了通过这种方式访问工作表时可以做什么和不能做什么。

只是想补充一下。 :)

@ShawnZhang 我们读取的大部分Excel文件都是以2000/2003格式导出的，因为发送者的程序就是这样发出的。我们已经测试了一种适用于2007/2010文件的连接字符串变体，也可以正常工作。 - AnonJr
@ShawnZhang 更重要的是，似乎选项卡名称（在方括号中，以$结尾）有一些奇怪的限制，我目前无法找到。列标题也是如此。 - AnonJr
2
Jet OLEDB 驱动在读取 Excel (所有版本及其前身 ODBC 驱动程序) 时存在内存泄漏问题：如果在任何用户会话中多次执行此操作，则会收到关于可用内存的错误消息。 忽略这些错误可能会导致您的应用程序最终冻结或崩溃。 微软不承认存在这个问题，也没有相关的文档记录。 - Nigel Heffernan
这也适用于ACE/DAO，使用以下语法：SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\somefolder\myfile.xlsx].[sheetName$]; 可无障碍地处理XLS和XLSX。 - iDevlop
5
iDevlop和Ashok的答案存在问题，根本问题是Excel设计缺陷（显然），其中Open方法未能尊重Application.ScreenUpdating设置为False。因此，将其设置为False对此问题没有任何好处。
如果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...
为什么需要使用 ActiveWindow 的操作？我认为在打开工作簿窗口后隐藏它会产生相同的效果。 - Winand
@Windand：如我上面所述，重点是尽快重新激活原始窗口，以最小化新打开的工作簿显示的时间。实际上，根据您的需求，隐藏已打开的工作簿是可选的。 - pstraton
@Windand：重新激活已经渲染的窗口图像非常快，而释放可见窗口则需要操作系统资源的处理，这无疑会更慢。这种差异是否足够重要？不得而知。 - pstraton
@pstraton，我尝试了你的解决方案，问题是Excel仍然不可见。如果您进行更改并保存它们，我的意思是下次打开Excel时，您将无法看到工作表，除非您手动转到“窗口/取消隐藏”... - JustGreat
3

将工作簿隐藏打开，然后将其设置为“已保存”，这样当用户关闭时就不会提示。

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

这在某种程度上是基于Ashok发布的答案。但是，通过这种方式，您将不会收到提示，要求您将更改保存回您正在阅读的Excel文件中。如果您正在阅读的Excel文件旨在用作验证的数据源，则这非常好。例如，如果工作簿包含产品名称和价格数据，则可以隐藏它，并显示代表发票的Excel文件，其中包含了从该价格列表验证的产品下拉列表。您可以将价格列表存储在网络共享位置上，并设置为只读。
在你的例子中，这本书并没有“作为隐藏状态打开”。它是先被打开，然后再被隐藏。打开过程仍然是可见的。 - johny why
1

在新的 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

完成后记得清理干净。

2
强调一下，在使用一个不可见的实例完成后，你必须进行清理。如果不这样做，用户可能无法通过双击或从其他应用程序启动来打开Excel文件，除非他们注销或知道如何终止进程（因为文件会在不可见窗口中打开）。 - David
0
在 Excel 中，隐藏工作簿并将它们保存为隐藏。当您的应用程序加载它们时，它们将不会显示。
编辑：重新阅读后，明确这些工作簿不是您应用程序的一部分。这样的解决方案对于用户工作簿来说是不合适的。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接