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
possible to connect with ADO a workbook....
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: possible to connect with ADO a workbook....
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:
And an example of changing a value in a specific row:
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
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
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: possible to connect with ADO a workbook....
tks!HansV wrote: ↑14 Nov 2021, 09:59The 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:
And an example of changing a value in a specific row: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
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
but i cannot test now....