Macro to subtotal in exported excel file

[edvapran]

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?

[edvapran]

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

[#3]

Maybe too late but Try This.

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

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