Excel 2007 and ADO connection PROBLEM for writing
Hi everybody,
First of all I'm sorry for my bad "english"...
I have a problem with Excel 2007 and ADO. I have made a small application in excel 2003 VBA and I'try to modify it for Excel 2007 but...it doesn't work. With my procedure, a value is written in cells J1 of my excel file base.xlsx. The value is always written in the first line but normally, it would be in cells J20 !!!
Sub Bouton2_Clic()
Dim Cn As ADODB.Connection
Dim Cd As ADODB.Command
Dim Rst As ADODB.Recordset
Dim Fichier As String
Dim NomFeuille As String
Fichier = "C:\toto\base.xlsx"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=NO;"""
.Open
End With
Set Cd = New ADODB.Command
Cd.ActiveConnection = Cn
Cd.CommandText = "SELECT * FROM [Feuil1$J20:J20]"
Set Rst = New ADODB.Recordset
Rst.Open Cd, , adOpenKeyset, adLockOptimistic
Rst(0).Value = "Donne test"
Rst.Update
Cn.Close
Set Cn = Nothing
Set Cd = Nothing
Set Rst = Nothing
End Sub
Thank you for your help !!!
Nathalie
