To put an automatic " End total " on column Currtotal

[Rama]

Hi There,

This query rund perfectly where I can extract data from
SAP via SQL server. However, I cannot get the " Sum total"
to appear automatically after end of data.

Please help , me to redefine the query in this respect.( I
tried in vain to use "compute(Currtotal)

Thanks a lot



Sub Trial()
Dim SQLstring, connstring As String
Dim Wkobj As Object
Dim Connobj As Object
Dim ObjParam1 As Object
Dim ObjParam2 As Object

'This is just to clearall on relevant sheet
Workbooks("SAPTrialBalance.xls").Worksheets(5).Activate
Workbooks("SAPTrialBalance.xls").Worksheets(5).Range
("A1:IV50000").ClearContents


SQLstring = " SELECT AcctName,Segment_0,CurrTotal " & _
" FROM OACT T0" & _
" where Segment_0>4999"


connstring = "ODBC;DSN=SAP Business One;APP=Microsoft
Office XP;WSID=NB-MELB-
05;DATABASE=MacDermidGraphicArts;LANGUAGE=us_english;Truste
d_Connection=Yes"

Set Wkobj = Workbooks("SAPTrialBalance.xls").Worksheets
(5).QueryTables
Set Connobj = Wkobj.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=SQLstring)
Connobj.Refresh

End Sub

 

[Nigel]

You'll need to detect the last row of the data loaded after the query is
run, then insert the total in the next available cell.
Try this.......

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow + 1, 1).Formula = "=sum(A1:A" & lastrow & ")"

Cheers
Nigel

 

[Rama]

Thanks a lot, it works

-----Original Message-----
You'll need to detect the last row of the data loaded after the query is
run, then insert the total in the next available cell.
Try this.......

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow + 1, 1).Formula = "=sum(A1:A" & lastrow & ")"

Cheers
Nigel




.