在Excel中使用VBA抑制数据链接属性对话框 [英] Suppressing the Data Link Properties dialog box in Excel using VBA

问题描述

使用VBA（.RefreshAll）刷新具有枢轴（与外部链接到另一个包含数据的Excel文件）的工作簿时，弹出一个名为数据链接属性的对话框。

我试图用DoEvents延迟来抑制对话框，Application.ScreenUpdating = False，Application.DisplayAlerts = False，OLEDB.BackgroundQuery = False。但似乎没有什么工作。

为了使事情变得更混乱，对话框不会定期弹出。有时它有，有时它不。

我的调试尝试只能导致一个理论：它可能是由数据文件的大小引起的，因此内存Excel正在占用。当这两个都很小时，对话框似乎不会弹出。当文件接近10mb时，对话框变得更加频繁。所以我觉得它与内存有关，即使至少有1 GB的可用内存。另外一个线索：如果我点击数据链接属性上的取消，然后点击功能区上的RefreshAll按钮：它就没有意义了给出一个未指定的错误和其他与数据文件相关的错误，即使该路径有效，该文件仍然存在。

任何对话框，如果我退出整个Excel应用程序，重新打开然后刷新。

（关闭Excel应用程序并通过脚本重新打开它不是我的选择，因为它会弄乱我的调用堆栈并结束执行流程。）

我只发现了另外两个人讨论这个链接：
首先没有回复，我可以不明白什么第二个说。

解决方案

在opendatabase之前简单地消除SendKeys，即：

SendKeys〜，True'包含true等待对话框打开

SendKeys〜，True，然后按确定（输入）

Workbooks.OpenDatabase _
...

While refreshing workbooks with pivots (that are externally linked to another Excel file containing the data) using VBA ( .RefreshAll), a dialog box titled "Data Link Properties" pops up.

I tried to suppress the dialog with DoEvents delays, Application.ScreenUpdating=False, Application.DisplayAlerts=False, OLEDB.BackgroundQuery=False. But nothing seems to be working.

To make matters more confusing, the dialog box doesn't pop up regularly. Sometimes it does, sometimes it doesn't.

My debugging attempts has lead to only one theory: it could be caused by the size of the data file, and thereby the memory Excel is taking up in the RAM. The dialog doesn't seem to pop up when both of these are small. When the file approaches more than 10mb, the dialog becomes more frequent. So I feel it has something to do with memory, even though there's at least 1 GB of available memory. This makes no sense but I've run out of ideas.

Another clue: if I click cancel on the data link properties and click the RefreshAll button on the ribbon: it gives "An unspecified Error" and other errors related to the data file not being found, even though the path is valid, and the file exists.

It will only refresh without any dialogs if I exit the entire Excel Application, reopen and then refresh.

(Closing the Excel Application and re-opening it via a script isn't an option for me, because it will mess up my call stack and end the execution flow.)

I've only found two other links of people discussing this: First has no replies and I can't understand what the second one is saying.

解决方案

Simply fire off SendKeys prior to opendatabase – i.e:

SendKeys "~", True 'include true to wait for dialog to open

SendKeys "~", True 'and press "ok" (by enter)

Workbooks.OpenDatabase _ ...

这篇关于在Excel中使用VBA抑制数据链接属性对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！