To put an automatic " End total " on column Currtotal
 

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).Act ivate
Workbooks("SAPTrialBalance.xls").Worksheets(5).Ran ge
("A1:IV50000").ClearContents


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


connstring = "ODBC;DSN=SAP Business One;APP=Microsoft
Office XP;WSID=NB-MELB-
05;DATABASE=MacDermidGraphicArts;LANGUAGE=us_engli sh;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

To put an automatic " End total " on column Currtotal
 

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" wrote in message
...
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).Act ivate
Workbooks("SAPTrialBalance.xls").Worksheets(5).Ran ge
("A1:IV50000").ClearContents


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


connstring = "ODBC;DSN=SAP Business One;APP=Microsoft
Office XP;WSID=NB-MELB-
05;DATABASE=MacDermidGraphicArts;LANGUAGE=us_engli sh;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

To put an automatic " End total " on column Currtotal
 

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

"Rama" wrote in
message
...
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).Act ivate
Workbooks("SAPTrialBalance.xls").Worksheets(5).Ran ge
("A1:IV50000").ClearContents


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


connstring = "ODBC;DSN=SAP Business One;APP=Microsoft
Office XP;WSID=NB-MELB-

05;DATABASE=MacDermidGraphicArts;LANGUAGE=us_engli sh;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


.