Please help to call excel subtotal function from powerbuilder application

Question

Hi,

Anybody have any idea to call  excel subtotal function   from power builder application.  The following is the code. My code is working fine now . Only I want add   subtotal function instead of SUM function . Please can u help me it would be very appreciate.

I want to add  the code following code with my existing code please help

 Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

My code as given  below

lole_excel = CREATE OLEObject

li_rtn = lole_excel.ConnectToNewObject("excel.application")

IF li_rtn <> 0 THEN

      MessageBox( "Error", 'Error running MS Excel application.')

      DESTROY lole_Excel

      RETURN false

END IF 

ole_excel = CREATE OLEObject

li_rtn = ole_excel.ConnectToNewObject("excel.application")

IF li_rtn  = 0  THEN

l_str_dir = is_filename

li_nextrow = l_lng_rowcnt + 1

li_lastrow = li_nextrow + 1

ole_excel.visible = false

ole_excel.Application.DisplayAlerts = False

ole_excel.WorkBooks.Open(l_str_dir) 

ole_workbook = ole_excel.application.workbooks(1)

ole_worksheet = ole_workbook.worksheets(1)

ole_excel.Application.Visible = True

ole_worksheet.Columns(1).HorizontalAlignment = -4108

ole_worksheet.Columns(5).HorizontalAlignment = -4108

ole_worksheet.Columns(7).HorizontalAlignment = -4108

ole_worksheet.Columns(8).HorizontalAlignment = -4108

ole_worksheet.Columns(9).HorizontalAlignment = -4108

ole_worksheet.Columns(10).HorizontalAlignment = -4108

ole_worksheet.Columns(11).HorizontalAlignment = -4108

ole_worksheet.Columns(9).NumberFormat = "#,##0"

ole_worksheet.Columns(10).NumberFormat = "#,##0"

ole_worksheet.Columns(11).NumberFormat = "£#,##0.00"

ole_worksheet.Range("A1:K1").Select

ole_worksheet.Range("A1:A"+string(li_nextrow)).Font.ColorIndex = 3

ole_worksheet.Range("A1:K1").Font.Bold = True

ole_worksheet.Columns("A:L").EntireColumn.AutoFit

ole_worksheet.Range("A1:K1").HorizontalAlignment = -4108

 li_nextrow =ole_worksheet.usedrange.rows.count

 li_lastrow = li_nextrow+1

ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Formula = "TOTAL"

ole_worksheet.Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Formula = "=SUM(K1:K"+string(li_nextrow)+")"

ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).Font.ColorIndex = 3

ole_worksheet.Range("F"+string(li_lastrow)+":F"+string(li_lastrow)).HorizontalAlignment  = -4108

ole_worksheet.Range("K"+string(li_lastrow)+":K"+string(li_lastrow)).Font.ColorIndex = 3

ole_workbook.SaveAs(l_str_dir, 39 ) ;

ole_workbook.Close() ;

ole_excel.Application.Quit

ole_excel.disconnectobject()

DESTROY ole_excel

End if

With Thanks

Pol

Answer 1

Sorry, I don't know PowerBuilder syntax, but from other examples it looks like the arguments should be in parentheses and named arguments are probably not supported so something like

ole_worksheet.Range("A1:K"+string(li_lastrow)).Subtotal(1,-4157, Array(2),1,0,1) 

The only bit I'm worred about in that is the Array(2).

If there's a way in PowerBuilder to make a single element array containing 2 and passing that, that's what's needed.

Answer 2

Before the SaveAs line:

ole_worksheet.Range("A1:K"+string(li_lastrow)).Subtotal GroupBy:=1, _

        Function:=xlSum, TotalList:=Array(2), _

        Replace:=True, PageBreaks:=False, SummaryBelowData:=True 

You probably will want to delete the previous 5 lines dealing with the total row.

Answer 3

Many Thanks for you reply .

When I am trying to apply the formule , syntax error is coming . May be it  is sybase power builder  Application problem.

Many thanks Again

Pol

Answer 4

Office