Suppressing the Data Link Properties dialog box in Excel using VBA

0
votes

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.

4
vbaexcel
Are you linking to workbooks over a network connection? if so, is it disconnected at the time? Try opening the folder to the linked workbook in Windows Explorer first and then open the workbook. Does that avoid the dialog? I am not convinced the size of the workbook/RAM are the culprits. – joehanna
@joehanna No the file is stored locally. Within the same folder in fact. I'll try keeping the data file open while refreshing, but I suspect it will just cause a "file currently in use" error. – Sulaiman
Hoping the famous @SiddharthRout might have a look at this... – Sulaiman

4 Answers

1
votes

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

0
votes

I ran into this same exact problem today. It seems there is a limit to how many connections there can be within a workbook. I kept recieving the "unspecified error" followed by the data link properties. The limit seems to be 70. I was able to fix it by going back to my separate workbooks and condensing the tabs even more. Hope this helps!

0
votes

What I found is that in connection string there is one string "data source" contains filename path and which does not have " sign with filename.wen I kept filenamepath in this it worked but wen I tried to replace this with variable contains filenamepath it pop ups the window data link properties...it solution must relates to this

0
votes

Had a similar issue and resolved it by changing

 Mode=Share Deny None;

in the connection properties.

Perhaps multiple connections were opening up separate instances of access and conflicting with each other creating a conflict.