title: Update Excel with ADODB with Workbook Open
body: <p>In Excel 2010. I am trying to write/update some cells, based on some filters, on the same workbook that executes the macro; for simplicity, I have reduced de query to what you can see.
  This code works, but only when the workbook is closed (tested from another workbook).</p>
  
  <pre><code>Dim libro As Workbook
  Set libro = ActiveWorkbook
  esteLibro = libro.Name
  
  Dim cnCaja As ADODB.Connection
  Dim rsCaja As ADODB.Recordset
  Set cnCaja = New ADODB.Connection
  Set rsCaja = New ADODB.Recordset
  cnCaja.Open "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
      "Data Source='" &amp; esteLibro &amp; "';" &amp; _
          "Extended Properties='Excel 12.0;HDR=Yes;ReadOnly=False';"
  
  Dim consCaja As String
  consCaja = "Update [Base$A3:T100000] Set INVERSIONES = 1000 "
  rsCaja.Open consCaja, cnCaja, adOpenStatic, adLockOptimistic
  </code></pre>
  
  <p>When executed from another workbook, the column "INVERSIONES" will have its blank cells changed to 10000; but from the same workbook I got the error:</p>
  
  <blockquote>
    <p>'-2147217865 (8004e37)' The Microsoft Access database engine could not
    find the object '[Base$A3:T100000]'. Make sure the object exists and that you spell its
    name and the path name correctly. If '' is not a local object, check
    your network connection or contact the server administrator.</p>
  </blockquote>
  
  <p>This is the entire connection string:</p>
  
  <pre><code>Debug.Print cnCaja.ConnectionString
  </code></pre>
  
  <blockquote>
    <p>Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin; Data
    Source=Danny_Diario.xlsm;Mode=Share Deny None; Jet OLEDB:System
    database="";Jet OLEDB:Registry Path=""; Jet OLEDB:Database
    Password="";Jet OLEDB:Engine Type=35; 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;</p>
  </blockquote>
  
  <p>And, a query against the same table will work, even when the workbook is open.</p>
  
  <pre><code>consCaja = "Select * from [Base$A3:T100000] Where Fecha = #" &amp; fechaBase &amp; "# and Empresa = ""Empresa"" and Banco = ""Banco"""
  rsCaja.Open consCaja, cnCaja, adOpenForwardOnly, adLockReadOnly
  
  Do While Not rsCaja.EOF
      MsgBox (rsCaja("Empresa").Value &amp; vbNewLine &amp; rsCaja("Banco").Value &amp; vbNewLine &amp; rsCaja("INVERSIONES").Value)
      rsCaja.MoveNext
  Loop
  </code></pre>
  
  <p>Finally, is it possible to update an Excel with ADODB with the workbook open?
  If yes, what am I doing wrong, or how do I resolve this?</p>