kiwimarie14
New Member
- Joined
- Jun 15, 2015
- Messages
- 1
I've come across a problem that no amount of googling is helping me with.
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've 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, both of which were no help.
Real frustrating head-scratcher! And so your inputs are highly appreciated! Thank you!
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've 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, both of which were no help.
Real frustrating head-scratcher! And so your inputs are highly appreciated! Thank you!
