Update in Excel using ADODB from VBA

Question

Update in Excel using ADODB from VBA

Navigation

#1 by (0 votes)

0

I can read the data of an Excel in a shared folder using a Recordset with ADODB ; I can write that data in a local file, but in the form Range().Value . I could not do it using a Update with ADODB .

This is my connection:

Dim cnCaja As ADODB.Connection
Set cnCaja = New ADODB.Connection
cnCaja.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='" & esteLibro & "';" & _
        "Extended Properties='Excel 12.0;HDR=Yes;ReadOnly=False';"

The idea is that it is something parameterized and with filters, but to simplify the problem:

consUpdate = "Update [Base$A3:T100000] set INVERSIONES = 10000"

And finally the part that should do something:

Dim rsCaja As ADODB.Recordset
Set rsCaja = New ADODB.Recordset

rsCaja.Open consUpdate, cnCaja, adOpenKeyset, adLockOptimistic

It does not give an error or say anything.

What is missing for the INVERSIONES field to be filled with 10000?

vba excel

asked by Jedicillo 10.01.2017 в 21:55

source

1 answer

Url with period "." in Django Rest Framework Error webmethod with form

score 0 · Answer 1

For posterity, the code works, but in another file. The variable esteLibro is constructed as:

Dim libro As Workbook
Set libro = ActiveWorkbook
esteLibro = libro.Name

But although I execute it from a book, it writes in another book (copy) that is in My Documents. Now I have the problem that, if I put the book with a route, to be sure of the book in which I write, it tells me that "Access does not find the Object", but that is another story ...