title: Using ADODB to access opened xls file
body: <p>Although I've been working with VBA for Excel for quite a long time, I've one problem I cannot solve by myself. I've described it below, hope to get some help or advice.<br>
  I'm using Excel 2007 and Windows XP, all updated with newest patches. </p>
  
  <p>I'm very often using following code to get data from another workbook:  </p>
  
  <pre><code>Set conn = New ADODB.Connection
  conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=g:\source.xls;Extended Properties=Excel 8.0;"
  
  Sql = "SELECT Field1, Field2 FROM [Sheet1$]"
  
  Set rst = New ADODB.Recordset
  rst.Open Sql, conn, adOpenForwardOnly
  
  Worksheets("Results").Range("A2").CopyFromRecordset rst
  
  rst.Close
  Set rst = Nothing
  
  conn.Close
  Set conn = Nothing
  </code></pre>
  
  <p>As simply as can be - just connect to file and get some data from it. It's working perfect as long, as the source file that is located on a common network drive (g:\source.xls) is not opened on another computer.<br>
  When some user on another computer has opened the file and I try to execute the following code, I notice one thing that I'd like to get rid off: <strong>the source Excel file is opened (in a read-only mode) on my computer and it's not closed after the connection to that file has been closed</strong>. What's worse, even if I close this source file manually, it leaves some garbage in my file, like it was never closed: see the picture after couple of code execution (the source files has been closed before):<br>
  <a href="https://i.stack.imgur.com/8cOPq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8cOPq.png" alt="enter image description here"></a></p>
  
  <p>I started to believe it's a bug that cannot be solved - hope I'm wrong :)</p>