locked
WorkbookConnection.Ranges returns errors/incorrect information in VSTO RRS feed

  • Question

  • Hi all,

    I have few workbooks with connections. Some workbook have power query and some have normal queries.

    I am trying to achieve for each workbook connection the ranges (or sheet information) where the corresponding workbook connection is being used through VSTO. The image below shows what I want as information for each workbook connection.

    I tried opening the workbook through C# VSTO add-in. When I loop over workbook connections and tried to get the Ranges for sometime it gave me the information (but not all the ranges, only gave me first range (Pivot Table)). On subsequent try, it did not give any ranges but gave me an exception.

    I guess this should be simple as this is only read-only information, but somehow I am not able to get through it. Any sample code would help

    - Girija

    Image


    Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria

    Friday, September 18, 2015 3:44 PM

All replies

  • >>>I guess this should be simple as this is only read-only information, but somehow I am not able to get through it. Any sample code would help

    According to your descritpion, I have created an Excel 2013 Workbook Document-Level Add-in , and get the Ranges it gave me the information when I loop over workbook connections.
    You could refer to the following code:

    private void ThisWorkbook_Startup(object sender, System.EventArgs e)
    {
        for (int i = 1; i <= this.Connections[1].Ranges.Count;i++ )
        {
            //Get Range information
            MessageBox.Show(this.Connections[1].Ranges[i].Address);
        }        
    }

    For more information, click here to refer about Range Properties (Excel)

    Tuesday, September 22, 2015 8:54 AM
  • Hi David,

    Thanks for looking at my question. But as I said it sometimes works and sometimes not. What I tried with was a workbook with Power query and models and some power pivot sheets.

    I was using a Application Level addin and when I opened the workbook and go to Connections and get it's range (all through C#), it threw me error. This works though when manually performing these steps.

    What I wanted to know that is there something to be done with these kind of reports (With power query and model) before checking the connection.ranges (like refresh and/or save it once again).

    I am asking this because I have lot of issues with VSTO working with Power Query or Power Pivot models recently.

    - Girija


    Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria

    Sunday, September 27, 2015 9:38 AM