possible to connect with ADO a workbook....

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

possible to connect with ADO a workbook....

Post by sal21 »

possible to connect with ADO a workbook.... and write into A1 or F%, or G34... ecc

my workbook is named c:\test\TEMPLATE.xlxs
my sheet is named LISTA

im' on VB6

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: possible to connect with ADO a workbook....

Post by HansV »

The worksheet must be laid out as a table, and you must write values to fields of that table. You cannot write values to an arbitrary cell such as G34 - that's not how ADO works.
Here is an example of adding a new row to a sheet with headers "ID" in A1 and "Nome" in B1:

Code: Select all

Sub TestAdd()
    Dim cnn As adodb.Connection
    Dim rst As adodb.Recordset
    Dim strFile As String
    Dim strSQL As String
    Set cnn = New adodb.Connection
    strFile = "C:\Excel\Book1.xlsx"
    cnn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set rst = New adodb.Recordset
    strSQL = "SELECT * FROM [Sheet1$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdText
    rst.AddNew
    rst!ID = 1
    rst!Nome = "Sal21"
    rst.Update
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
And an example of changing a value in a specific row:

Code: Select all

Sub TestModify()
    Dim cnn As adodb.Connection
    Dim rst As adodb.Recordset
    Dim strFile As String
    Dim strSQL As String
    Set cnn = New adodb.Connection
    strFile = "C:\Excel\Book1.xlsx"
    cnn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set rst = New adodb.Recordset
    strSQL = "SELECT * FROM [Sheet1$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdText
    rst.Find "ID=1"
    rst!Nome = "Salvatore"
    rst.Update
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: possible to connect with ADO a workbook....

Post by sal21 »

HansV wrote:
14 Nov 2021, 09:59
The worksheet must be laid out as a table, and you must write values to fields of that table. You cannot write values to an arbitrary cell such as G34 - that's not how ADO works.
Here is an example of adding a new row to a sheet with headers "ID" in A1 and "Nome" in B1:

Code: Select all

Sub TestAdd()
    Dim cnn As adodb.Connection
    Dim rst As adodb.Recordset
    Dim strFile As String
    Dim strSQL As String
    Set cnn = New adodb.Connection
    strFile = "C:\Excel\Book1.xlsx"
    cnn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set rst = New adodb.Recordset
    strSQL = "SELECT * FROM [Sheet1$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdText
    rst.AddNew
    rst!ID = 1
    rst!Nome = "Sal21"
    rst.Update
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
And an example of changing a value in a specific row:

Code: Select all

Sub TestModify()
    Dim cnn As adodb.Connection
    Dim rst As adodb.Recordset
    Dim strFile As String
    Dim strSQL As String
    Set cnn = New adodb.Connection
    strFile = "C:\Excel\Book1.xlsx"
    cnn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFile & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set rst = New adodb.Recordset
    strSQL = "SELECT * FROM [Sheet1$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdText
    rst.Find "ID=1"
    rst!Nome = "Salvatore"
    rst.Update
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
tks!
but i cannot test now....