X
xoco
Hi,
Right now, i now how to read and write in a closed file using ADO, but
i'd like to write each time in the last line, or insert a line at the
beginning of the document, so that insertion could be ordenated...
I'm using a code from rob bovey, -->
' 2 - Ecrire dans un classeur fermé
Sub EcritDatas()
Dim Fich$, cell As Range
Dim i As Integer
Fich = "c:\TEST.xls" '
' i = Fich
SetExternalDatas Fich, "Hoja1", cell.End(xlDown).Address(0, 0), "aa"
SetExternalDatas Fich, "Hoja1", cell.End(xlDown).Address(0, 0), "zz"
'SetExternalDatas Fich, "Hoja1", "A6", "mise à jour du " & Now
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 ocmd2 = New ADODB.Command
ocmd2.ActiveConnection = oConn
RangeDest = "A:F"
ocmd2.CommandText = "SELECT * from `" & DestFeuille & "$" &
RangeDest & "`"
'----------------------------
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn
' This SQL statement selects a cell range in the "feuilleTest"
worksheet.
RangeDest = DestCellAdr & ":" & DestCellAdr
oCmd.CommandText = "SELECT * from `" & DestFeuille & "$" & RangeDest
& "`"
' 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
if you gotta an answer, it would be appreciated, because i really
don't i've an idea to keep on this stuff.
XoCo
Right now, i now how to read and write in a closed file using ADO, but
i'd like to write each time in the last line, or insert a line at the
beginning of the document, so that insertion could be ordenated...
I'm using a code from rob bovey, -->
' 2 - Ecrire dans un classeur fermé
Sub EcritDatas()
Dim Fich$, cell As Range
Dim i As Integer
Fich = "c:\TEST.xls" '
' i = Fich
SetExternalDatas Fich, "Hoja1", cell.End(xlDown).Address(0, 0), "aa"
SetExternalDatas Fich, "Hoja1", cell.End(xlDown).Address(0, 0), "zz"
'SetExternalDatas Fich, "Hoja1", "A6", "mise à jour du " & Now
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 ocmd2 = New ADODB.Command
ocmd2.ActiveConnection = oConn
RangeDest = "A:F"
ocmd2.CommandText = "SELECT * from `" & DestFeuille & "$" &
RangeDest & "`"
'----------------------------
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn
' This SQL statement selects a cell range in the "feuilleTest"
worksheet.
RangeDest = DestCellAdr & ":" & DestCellAdr
oCmd.CommandText = "SELECT * from `" & DestFeuille & "$" & RangeDest
& "`"
' 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
if you gotta an answer, it would be appreciated, because i really
don't i've an idea to keep on this stuff.
XoCo