Hi all,
I have found some odd VBA behaviour which I cannot understand.
I have created a dashboard in excel, which pulls data in from a SQL database and also from another separate spreadsheet. I have written some VBA to refresh the data tables, assemble them into a single table and then refresh multiple pivot tables and charts hanging off this assembled table. This essentially allows users to refresh the dashboard themselves.
My problem occurs when the source spreadsheet is locked for editing (which is most of the working day). When this is the case, my dashboard will open the source spreadsheet, switching focus away from the dashboard itself and understandably causing the VBA to fall over on the subsequent step.
I could easily amend my VBA to switch focus back to the dashboard and even close the source sheet - but why is this even happening? Really I would prefer it just didn't try and open the data source in the first place.
The line of VBA code responsible for refreshing the data is simply:
The connection string to the source spreadsheet is as follows:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Quality\Defect Analysis\Defect data 2017 v5.xlsx;Mode=Read;Extended Properties="ReadOnly=True;HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;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
I must confess I don't understand much about the connection string, however when I first set the connection up, despite choosing "Read" from the initial Connection Properties, there was originally the line 'Mode=Share Deny Write', however I changed this to 'Mode=Read' in attempting to fix this issue. Needless to say it did not work!
Any assistance would be appreciated, please let me know if I can provide any more pertinent information
Cheers
John
I have found some odd VBA behaviour which I cannot understand.
I have created a dashboard in excel, which pulls data in from a SQL database and also from another separate spreadsheet. I have written some VBA to refresh the data tables, assemble them into a single table and then refresh multiple pivot tables and charts hanging off this assembled table. This essentially allows users to refresh the dashboard themselves.
My problem occurs when the source spreadsheet is locked for editing (which is most of the working day). When this is the case, my dashboard will open the source spreadsheet, switching focus away from the dashboard itself and understandably causing the VBA to fall over on the subsequent step.
I could easily amend my VBA to switch focus back to the dashboard and even close the source sheet - but why is this even happening? Really I would prefer it just didn't try and open the data source in the first place.
The line of VBA code responsible for refreshing the data is simply:
Code:
'Refresh source data
ActiveWorkbook.RefreshAllThe connection string to the source spreadsheet is as follows:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Quality\Defect Analysis\Defect data 2017 v5.xlsx;Mode=Read;Extended Properties="ReadOnly=True;HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;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
I must confess I don't understand much about the connection string, however when I first set the connection up, despite choosing "Read" from the initial Connection Properties, there was originally the line 'Mode=Share Deny Write', however I changed this to 'Mode=Read' in attempting to fix this issue. Needless to say it did not work!
Any assistance would be appreciated, please let me know if I can provide any more pertinent information
Cheers
John
