1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| Sub EcritDatas()
Dim Fich$, cell As Range
Fich = ThisWorkbook.Path & "\C.xls" ' adapter
'Ècrit dans le classeur fermÈ la valeur des cellules A1:E5
'du classeur actif
For Each cell In ActiveWorkbook.Sheets("Feuil1").Range("A5:E5")
SetExternalDatas Fich, "Feuil1", cell.Address(0, 0), cell.Text 'cell.Address(0,0), cell.Text
Next
'Ècrit en A6 la date et l'heure de l'opÈration
'SetExternalDatas Fich, "Feuil1", "A6", "mise jour du " & Now
'on regarde le rÈsultat
DoEvents
Workbooks.Open Fich
End Sub
'Ècrit DataToWrite dans la cellule DestCellAdr
'de la feuille DestFeuille du classeur fermÈ DestFile
Sub SetExternalDatas(DestFile As String, _
DestFeuille As String, _
DestCellAdr As String, _
DataToWrite As Variant)
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset
Dim RangeDest
'd'aprËs Rob Bovey, mpep
' Open a connection to the Excel spreadsheet
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DestFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;"";"
' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn
' This SQL statement selects a cell range in the "feuilleTest" worksheet.
'1 SÈlection pour Ècrire dans une seule cellule
'DestCellAdr = "A2
RangeDest = DestCellAdr & ":" & DestCellAdr
oCmd.CommandText = "SELECT * from `" & DestFeuille & "$" & RangeDest & "`"
Stop
' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic
' Update last row
oRS(0).Value = DataToWrite
oRS.Update
'Close the connection
oConn.Close
Set oConn = Nothing
Set oCmd = Nothing
Set oRS = Nothing
End Sub |
Partager