Expanding PivotTable Source Range

[Preston Cole]

Hi all, loving the F# life!

We have an external process that appends data to source sheets. The goal is to expand these source ranges for every PivotTable in each given workbook to include the appended data, but I keep running into the dreaded 'System.Runtime.InteropServices.COMException' no matter how much I modify the code below.

Am I missing something obvious? The file closes without saving, so there must be an unhandled exception derailing my try clause.

Please feel free to drop any research links and make me earn my solution. Thanks!

...

let expandPivotSourceRanges (filePath:string) =
    let xlApp = new ApplicationClass(Visible = true, DisplayAlerts = true)
    let workbook = xlApp.Workbooks.Open(filePath)

    let sheetNames =
        [ for sheetIndex in 1 .. workbook.Sheets.Count do
            let sheet = workbook.Sheets.Item(sheetIndex)
            match sheet with
            | :? Worksheet as worksheet -> worksheet.Name
            | _ -> null ]

    let nonNullSheetNames = List.filter (fun x -> x <> null) sheetNames

    for sheetName in nonNullSheetNames do
        let sheet = workbook.Sheets.Item(sheetName) :?> Worksheet
        try
            let pivotTable = sheet.PivotTables(1) :?> PivotTable
            let sourceRange = sheet.Range(pivotTable.SourceData)
            pivotTable.SourceData <- sourceRange.Worksheet.UsedRange
            Marshal.ReleaseComObject(pivotTable) |> ignore
            Marshal.ReleaseComObject(sourceRange) |> ignore
        with
        | :? System.Runtime.InteropServices.COMException ->
            xlApp.StatusBar <- $"{sheet.Name} does not have a pivot."

        Marshal.ReleaseComObject(sheet) |> ignore

    workbook.Close(SaveChanges = true)
    xlApp.Quit()
    Marshal.ReleaseComObject(workbook) |> ignore
    Marshal.ReleaseComObject(xlApp) |> ignore

[Govert van Drimmelen]

Hi Preston,

 

Are you running this code from an external application like a console app, or from a macro inside an Excel add-in?

I assume the former. The Excel-DNA project is making Excel add-ins, so might not be the best place for general Excel programming support.

 

You should not need to call Marshal.ReleaseComObject to clean things up, just structure the code so that you can run the GC after all the Excel work is complete.

See my Stackoverflow answer here: https://stackoverflow.com/a/38111137/44264

 

It might help you to check whether similar code works from VBA.

That could at least pin down the exact call, and maybe a better error message.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/a7585141-2fd5-4628-b199-e45ef46c4165n%40googlegroups.com.

[Preston Cole]

Hi Govert,

This code is running from within an Excel add-in/extension. I currently use a similar, though simpler, version in VBA:

    CurrentFile = Dir(CurrentPath & "*.xls*")

    Do While CurrentFile <> ""

        If CurrentFile <> ThisWorkbook.Name Then

            Set wb = Workbooks.Open(CurrentPath & CurrentFile)

            For Each ws In wb.Worksheets

                For Each pt In ws.PivotTables

                    pt.RefreshTable

                Next pt

            Next ws

           

            wb.Close SaveChanges:=True

        End If

       

        ' Move on to the next file

        CurrentFile = Dir

    Loop

The only difference here is that I now want to resize the underlying source range of the PivotTable in addition to refreshing it, but somewhere between accessing, capturing, and altering the PivotTable source range: a COM exception fires. I've gotten so used to trusting the type system that I'm surprised this is the first time invalid type casting was propagated to the runtime before ChatGPT saved my soul:

let pivotTable = sheet.PivotTables(1) :?> PivotTable

let sourceRange = sheet.Range(pivotTable.SourceData)

pivotTable.SourceData <- sourceRange.Worksheet.UsedRange

Please feel free to ignore this post if this is a foolishly trivial question. I'm spitballing new approaches in case I've reached a dead-end.

Cordially,

Preston

[Govert van Drimmelen]

With the F# code you show

    let xlApp = new ApplicationClass(Visible = true, DisplayAlerts = true)

I don’t know what this will do from inside the add-in – whether it opens a separate Excel process, or connect to the current Excel instance where the ad-in is running.

I suggest you run the code in the same Excel instance as the add-in, and that you get hold of the Excel root Application object by calling

“ExcelDnaUtil.Application” – the return type is object, but you can cast to an “Microsoft.Office.Interop.Excel.Application” type.

 

Then you must definitely remove all the ReleaseObject calls, and also not Quit the application.

 

If you then still have a problem, you need to identify the exact line, and try to match with what happens in the matching VBA.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6665b5b4-ef74-42fd-92aa-cd284d3db029n%40googlegroups.com.