Change Data Connection to Read Only
<p>I have a "summary" Excel workbook with pivots and data connections to other "data" workbooks.</p>

<ol>
<li>When refreshing the connection and a user is in the data workbook the refresh falls over saying it is read only. (It then tries to open the data workbook.) Is there a way around this? E.g. read the data rather than needing write access? Data String properties below.</li>
</ol>

<blockquote>
  <p>Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Test\Test.xlsm;Mode=Share Deny Write;Extended Properties="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;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False</p>
</blockquote>

<ol start="2">
<li>When I have refreshed the connections and a user goes into the data workbooks, excel says it read only and used by "Another user".</li>
</ol>

<p>I'm refreshing using VBA <code>ActiveWorkbook.Connections("Test.xlsm").Refresh</code>.</p>

<p>Is there a way in VBA to tell Excel to drop the access so the data workbook becomes free. The only solution I can think of is to shut down the workbook and open again.</p>