finding the last row of a query retriving external data 1

[Matsul]

I need to return data into an xls sheet using an external query ran sequencially on different oracle partitions.

ie I run

Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=rng, _
Sql:=rSQL)

oQt.Refresh


many times.

To set the range I identify the last row using


Function bottom_row(sh As String) As Integer

Worksheets(sh).Activate
Range("a1").Select

For i = 1 To 65536


If (Cells(i, 1).Value = "" And Cells(i + 1, 1).Value = "" And Cells(i + 2, 1).Value = "" And Cells(i + 3, 1).Value = "") Then
bottom_row = i
i = 68000
End If
Next i


which I know can be done using Xldown but I ended up doing as above to try to understand further.


However, the last row seems to be the row where the last heading is meaning the function will return 4 for the below.

If I stop the procedure and run the function seperately I will get 6. Seems something is not refreshed and the program thinks the last two rows have no data. Any ideas ?

NORMALISED_EVENT_ID
12320871120
12320871120
NORMALISED_EVENT_ID
12320871120
12320871120

 

[PHV]

You may try this:
oQt.Refresh [!]False[/!]

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