Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
Need column evaluated to count total of occurrences of "Y". How? | Excel Worksheet Functions | |||
Linking two "total" pages to create a "Complete Total" page | Excel Worksheet Functions | |||
Adding "TOTAL" and summing column | Excel Programming |