none
C# refresh Power Pivot Query Excel RRS feed

  • Question

  • I have done some quite extensive research and I have been unable to find the answer anywhere.

    So to summarise, I have a VSTO workbook with a number of queries that are feeding Power Pivot and all I would like to do is refresh the data.... but I cannot use RefreshAll as only certain queries need refreshing at certain times.

    I have done it in VBA very easily and when I try the following code in C# I get a COM exception

     //Defining Excel objects
            Excel.Application xlApp = Globals.ThisWorkbook.Application;
            Excel.Workbook workbook = xlApp.ActiveWorkbook;
            Excel.Sheets sheets = workbook.Worksheets;
    
            //Refreshing connections
            foreach (Excel.WorkbookConnection Con in workbook.Connections)
            {
                if (Con.Name!="Query - CMC's")
                {
                    Con.Refresh();
                }
             }

    Please can someone help me? Also I cannot find any API's to do with Power Query, which I know was the case back in 2012 but cannot find anything to say they have updated it since.

    Thanks for you help.

    Joe


    • Edited by JoeyStyles Thursday, October 12, 2017 11:24 PM
    Thursday, October 12, 2017 11:23 PM

All replies

  • Hello,

    What code do you use in VBA? What is the detail exception message in C#?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 13, 2017 5:46 AM
    Moderator
  • Hi Celeste,

    This is the error:

    System.Runtime.InteropServices.COMException occurred
      HResult=0x800A03EC
      Message=Exception from HRESULT: 0x800A03EC
      Source=
      StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.WorkbookConnection.Refresh()
       at Smart_View.Ribbon1.DataRefresh(IRibbonControl Control) in D:\Visual Studio\Projects\Smart View\Smart View\Ribbon1.cs:line 399

    Here is the vba code

    Sub test()
    
    Dim con As WorkbookConnection
    
    For Each con In ThisWorkbook.Connections
    
    If con.Name <> "Query - CMC's" Then
    con.Refresh
    End If
    
    Next con
    
    End Sub
    

    Friday, October 13, 2017 10:01 AM
  • Hello,

    If the method works using VBA, it should work in C# as well.

    I follow Refresh a query (Power Query) to create a query and the refresh method works fine for me in VBA or C#. 

    How do you create the query? Would the code fails for all queries if you refresh then one by one. Could you reproduce the issue in other document or a new created query? 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 16, 2017 9:05 AM
    Moderator
  • Very stupid product, it only works in VAB, not C#
    Wednesday, July 18, 2018 11:49 PM