Update Excel with ADODB with Workbook Open

Question

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).

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;" & _
    "Data Source='" & esteLibro & "';" & _
        "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

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:

'-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.

This is the entire connection string:

Debug.Print cnCaja.ConnectionString

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;

And, a query against the same table will work, even when the workbook is open.

consCaja = "Select * from [Base$A3:T100000] Where Fecha = #" & fechaBase & "# and Empresa = ""Empresa"" and Banco = ""Banco"""
rsCaja.Open consCaja, cnCaja, adOpenForwardOnly, adLockReadOnly

Do While Not rsCaja.EOF
    MsgBox (rsCaja("Empresa").Value & vbNewLine & rsCaja("Banco").Value & vbNewLine & rsCaja("INVERSIONES").Value)
    rsCaja.MoveNext
Loop

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?

Connection strings need full path. Change top line to: esteLibro = libro.FullName. Even still, I doubt you can update with opened workbook. — Parfait
I can read, so full path is not necessary. But even with full path, I still have the same error. And I have been unable to find some source that tells me that this cannot be done. — Jedicillo

Parfait Parfait · Accepted Answer · 2017-01-11T22:19:22

Consider running a read-only SELECT over the existing data of the opened workbook. For various reasons of file write access, Excel, being a flat file cached memory application, may not have the facility like databases to have application opened and current data updated synchronously.

Below sets up SELECT query for you to mirror current structure with one change of passing a constant, 1000, and assigning it to the INVERSIONES column alias in current column position. A conditional logic IIF statement can easily be placed here as well. Alternatively, run query and dump results in a new worksheet. Then delete/archive current worksheet and save workbook.

Dim cnCaja As New ADODB.Connection
Dim rsCaja As New ADODB.Recordset
Dim consCaja As String

esteLibro = ActiveWorkbook.FullName
cnCaja.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source='" & esteLibro & "';" & _
            "Extended Properties='Excel 12.0;HDR=Yes;';"

consCaja = "SELECT Col1, Col2, Col3, '1000' AS INVERSIONES, Col4, Col5, Col6" & _
           " FROM [Base$A3:T100000]"
rsCaja.Open consCaja, cnCaja

Worksheets("Base").Range("A3").CopyFromRecordset rsCaja

rsCaja.Close: cnCaja.Close
Set rsCaja As Nothing: Set cnCaja As Nothing

Update Excel with ADODB with Workbook Open

1 Answers