A QUESTION OF TIME - 500 POINTS

A QUESTION OF TIME - 500 POINTS

In attention: WATYF (and not only…)
             Dear WATYF, this problem is another way of presentation (with more details and different approach) of my old problem posted with title:" Automatic update - partial table ".
             At the end of our discussion you recommended me to try ADO (ActiveX Data Objects). I didn't know a thing about ADO, but in few days I learned and I had success. VBA with ADO is working better than your simple code, but I am still not satisfied. I need to minimize the access time more. For this reason I am posting the problem again with all details and more points.
            
Introduction:

      I have a csv file in this folder C:\TEMP\ TEST1.CSV      . This file is pretty big: few MB, few thousands of lines (around 12 MB at the end of the day) and is created automatic by an exe program in the beginning of each day (and is used like a database). At each 10-15 sec new data is appended to this csv file.
      I have also a xls file in this folder C:\My Documents\TEST2.xls. I need to take all the data from csv file into xls file (in Sheet1). Actually I need to make a mirror of csv file in Excel.
      Nobody is changing data (like delete) in csv file. There are no empty lines in csv file.
      As soon as new data is added to csv file I need to have it in my xls file - systematic delay of max. 10 sec is accepted. It is like I need to transfer data from that csv file in xls file in "almost" real-time.

What I tried first time:

      WATYF gave me this code:

Sub ImportData()

Application.ScreenUpdating = False

MainWb = "TEST2.xls"
MainSh = "Sheet1"
CSVPth = "C:\TEMP\"
CSVFl = "TEST1.CSV"

i = Workbooks(MainWb).Sheets(MainSh).Range("A65536").End(xlUp).Row

Workbooks.Open (CSVPth + CSVFl)
x = Workbooks(CSVFl).ActiveSheet.Range("A65536").End(xlUp).Row
If x <> i Then
For y = i To x
Workbooks(CSVFl).ActiveSheet.Rows(y).Copy Workbooks(MainWb).Sheets(MainSh).Range("A" & y)
Next
End If

Workbooks(CSVFl).Close False
Application.ScreenUpdating = True

End Sub

Problems:

      The code is working, but…
      The time necessary to open and close a 10MB csv file is too big (on my PC is bigger than 60 sec.) and on those moments my PC is busy, the exe program can't write in csv file because is opend by my VBA code, and with that kind of delay we are not talking about "close to real-time" reading.
      After that, WATYF recommended me to try ADO. So, I wrote the next VBA code (and is working, but still…):

What I tried second time:

Public cn As ADODB.Connection, rs As ADODB.Recordset

Sub GetTextFileData(strSQL As String, strFolder As String, TargetCell As Range)
Dim f, RowNo As Integer
Dim count As Long

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Conecting to csv file..."
    End With

    If TargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenDynamic, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
     
RowNo = Workbooks("TEST2.xls").Sheets("Sheet1").Range("A65536").End(xlUp).Row
   
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .StatusBar = "Searching trough recordset..."
End With
   
rs.Move RowNo
RS2WS TargetCell, RowNo

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    GetTextFileData "SELECT * FROM TEST1.csv", "C:\Temp\", Range("A1")
    ActiveWorkbook.Saved = True
End Sub

Sub RS2WS(TargetCell As Range, RowNo As Integer)
Dim f As Integer, r As Long, c As Long

    If rs Is Nothing Then Exit Sub
    If rs.State <> adStateOpen Then Exit Sub
    If TargetCell Is Nothing Then Exit Sub
   
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Reading the new data..."
    End With
   
    With TargetCell.Cells(RowNo, 1)
        r = .Row
        c = .Column
    End With
   
   With TargetCell.Parent
        Do While Not rs.EOF
            r = r + 1
            For f = 0 To rs.Fields.count - 65
                On Error Resume Next
                .Cells(r, c + f).Formula = rs.Fields(f).Value
                On Error GoTo 0
            Next f
            rs.MoveNext
        Loop
        .Columns("A:IV").AutoFit
    End With
       
    With Application
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Less problems, but…still:
      
      This last code is working better than first one - is quicker, but still take too much time.

Questions:

1) The usage of UDL file will solve my problem with time? How can I do that?
2) Does anybody know a different way to solve this problem? I mean to retrieve the data in a short time (like 5 sec or less).

Discussion about VBA code with ADO (the second code):

Now I will present the ideas of code:
- Open a database connection (using ADO)
- Open a recordset
- Find out how many records are in my xls sheet
- Move trough the recordset to the point from where I will read just the new data
- Read just the new data down to EOF
- Write the new data - append - in my xls sheet
- Close the recordset
- Close the database connection


Ideas and observations:

      What makes the code to be slow is rs.Move, because everytime when I reopen the connection I have to move down (forward) to that point where new data was written - that means jump over thousands of records each time.  
      I think the access time can be improved if I will stay connected all the time trough ADO at the recordset, at that csv file and if my connection is some how dynamic and if I can share somehow that csv file in the next way:  I am connected to that csv file and also that exe program is connected and I can see when the exe program is writing data to the csv file.
      I need something like Refresh or Update (and I tried from ADO without success - probably wrong way) everytime when the exe program is writing the new data.
      If this kind of thing is possible then I have the next idea: to use the rs.Move just once, keep the connection open and next time when I will read (let's say in the next 10 sec) I will just use the read-write procedure.
      The read procedure is done  down to EOF. If something like I said above it will happen (like a Refresh or Update, with sharing from exe program) then I will have continuos "movement" of EOF and I can use just the read-write procedure.
      From my observation I can say that exe program cannot write into csv file until I close the recordset and ADO connection.

Last questions:

      With reference at what I said above: how can I "share" may csv file between that exe program and VBA code using ADO (or something else)? Normal sharing is not possible. It can be done just for excel files (xls) - as much as I know.
      How can  I make that ADO "dynamic" with that possibility of Refresh, so I can see in real-time the new data added by exe program?

      Any other idea or suggestions, even a different way of approach  (not necessary using ADO), will be great help.
Thank you for your time (I know it's a long story).
Victor.