Adjust visible rows after QT refresh 2

[HughLerwill]

Dear All,

Excel

I have set up a QueryTable from a sheets Data menu which reads the complete contents of a log file (text) and refreshes once a minute. The contents of the complete file, getting longer appears in the sheet.

How can I ensure the last filled row is visible on the screen after each refresh, currently it disappears off the bottom after a few.

TIA Hugh

 

[SkipVought]

hi,

Tune something like this...

With ActiveSheet.QueryTables(1).ResultRange  'assuming only ONE QT
    Set r = Cells(.Rows.Count - ActiveWindow.VisibleRange.Rows.Count / 2, 1)
    r.Select
End With

Skip,

_[red][/red]

 

[HughLerwill]

Skip,

Thanks.

How do I get that to fire when the QT refreshes?
I seem to be needing an AfterRefresh event or an equivalent.

regards Hugh

 

[PHV]

Just a though: why not getting the log file in DESC order of the timestamps ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Skip,

Yes I'm finding your idea is working; I'm using the Worksheet_Change event to fire

Set r = .Cells(.Cells.SpecialCells(xlLastCell).Row - ActiveWindow.VisibleRange.Rows.Count / 3, 1)
r.Select

Would be good if the number of empty rows after the last filled one stayed constant but at least the last one seems to remain visible. A little more tuning ...

PHV,
Are you suggesting I do something to the QT to change the sort order, I can't find anything obvious to switch, and I don't think SQL is available on text files (could be wrong). I could do a sort after the refresh of course but building that into the QT would be preferable and quicker I expect.

Thanks to you both.

 

[SkipVought]

Sorry,

I almost always refresh my QT's via code.

    With wsSchDel.QueryTables(1)
        .Connection = Array(Array( _
        "ODBC;DSN=DWPROD;;DBQ=DWPROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=" _
        ), Array("F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
        With .ResultRange  'assuming only ONE QT
          Set r = Cells(.Rows.Count - ActiveWindow.VisibleRange.Rows.Count / 2, 1)
          r.Select
        End With
      
    End With

Or you could use the AfterRefresh Event -- however, you must add a class for this. Check in

http://www.microsoft.com

Skip,

_[red][/red]

 

[combo]

You can use querytable events, create WithEvents variable, assign the querytable to it, write event provedure. For instance:

ThisWorkbook module:

Private Sub Workbook_Open()
Set Sheet1.qt = Sheet1.QueryTables(1)
End Sub

Sheet1 module (assuming the querytable is here, Sheet1 - code name):

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
' the code here
End Sub

combo

 

[HughLerwill]

Thanks to you all for your help.
regards Hugh