last cell of a closed file

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top