Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
last cell of a closed file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference a closed filed from a cell and formula in an open file | Excel Worksheet Functions | |||
Copying cell value to master report file which is closed? | Excel Worksheet Functions | |||
Offset on closed file | Excel Discussion (Misc queries) | |||
Excel is not asking to save a changed file when the file is closed | Excel Discussion (Misc queries) | |||
Reading from a closed file. | Excel Worksheet Functions |