Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

edvapran
New Contributor II

Macro to subtotal in exported excel file

Hi,

I'm trying to use subtotal function in exported excel file.

Set oXL=CreateObject("Excel.Application")   

f_name="C:\test\test.xls"

    Set oWB=oXL.Workbooks.Open(f_name)

    oXL.Visible = True

    oXL.Range("A1:Q156").Select

    oXL.Selection.Subtotal 1, xlCount, Array( 2, 3 ), False, False, False

I get an error "Subtotal method of Range class failed"

But when I copy the same function to excel it all goes well.

Selection.Subtotal 1, xlCount, Array( 2, 3 ), False, False, False

Anyway I can get this to work?

Tags (3)
2 Replies
edvapran
New Contributor II

Re: Macro to subtotal in exported excel file

Or a simple script fails:

    Set xlApp = CreateObject("Excel.Application")   

    Set xlBook = xlApp.Workbooks.Add

    Set xlSheet = xlBook.Worksheets(1)

    xlApp.Visible = True

    xlSheet.Visible = True

    xlApp.Worksheets("Sheet1").Activate

    xlSheet.Activate

    xlSheet.Range("A1").Value = "field1"

    xlSheet.Range("B1").Value = "field2"

    xlSheet.Range("A2").Value = "test"

    xlSheet.Range("B2").Value = 1

    xlSheet.Range("A3").Value = "test"

    xlSheet.Range("B3").Value = 2

    xlSheet.Range("A1:B3").Select

    xlSheet.Range("A1:B3").Subtotal 1, xlSum, 2, True, False, False

Not applicable

Re: Macro to subtotal in exported excel file

Maybe too late but Try This.

objCurrentSheet.Range("A:F").Select

  objExcelApp.Selection.Subtotal 1, -4157, array(6), True, False, False

Community Browser