1

I have a tool.xlsx file that uses external data connection to Access DB (both are on a network drive, available to users with proper access rights). I have a vbscript (code below) that refreshes all the connections in that workbook. Everything works like charm when I am running this script but when a colleague runs it, it throws no errors but not all the connections are refreshed. Any ideas?

Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = false
xlapp.workbooks.open "\\networkpath\tool.xlsx"
xlapp.displayalerts = false
set wr = xlapp.workbooks.Open("\\networkpath\tool.xlsx")
wr.refreshall
xlapp.visible = false
wr.Save
wr.Close

And here is the connection string from that tool.xlsx workbook:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\networkpath\db.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

Here's a different approach at refreshing each connection at once:

on error resume next
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = false
xlapp.displayalerts = false
set wr = xlapp.workbooks.open("\\networkpath\tool.xlsx")
    for each Cn in wr.Connections
        Cn.Refresh
    next
wr.refreshall
xlapp.visible = false
wr.Save
wr.Close
Community
  • 1
  • 1
Adrian Chrostowski
  • 424
  • 9
  • 30
  • First, why does your script open the workbook twice? Second, is the data connection set to refresh in the background? – Rory Jul 23 '15 at 13:23
  • First - good question. Second - data connection refresh in the background is disabled. – Adrian Chrostowski Jul 23 '15 at 13:53
  • How many connections are there? – Rory Jul 23 '15 at 13:55
  • Well, it's a little more complex here as this error/flaw is happening over multiple excel files I use in the same manner. Back to your question, number of connections varies between 6 and 40 depending on a file. I have also tried a little different way to refresh the connections one by one as in the script added at the end of original question. – Adrian Chrostowski Jul 23 '15 at 14:00

0 Answers0