Expanding PivotTable Source Range

30 views
Skip to first unread message

Preston Cole

unread,
Sep 7, 2023, 10:02:28 PM9/7/23
to Excel-DNA
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

unread,
Sep 8, 2023, 2:43:01 AM9/8/23
to exce...@googlegroups.com

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

unread,
Sep 8, 2023, 3:45:54 AM9/8/23
to Excel-DNA
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

unread,
Sep 8, 2023, 3:52:54 AM9/8/23
to exce...@googlegroups.com

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.

Reply all
Reply to author
Forward
0 new messages