none
ListObject.QueryTable Before/After refresh problem RRS feed

  • Question

  • Hi,

    I'm having a trouble with the before/after refresh events of a querytable under excel 2007/2010

     

    What I'm trying to achieve:

    Measure the time that it takes for the query table to refresh its data. (fetching data from sql db)

    I would like to measure this time any time the user refresh the table. (manually by clicking on the refresh button / context menu)

     

    My problem:

    My event handlers are only been called once, after the first refresh of the table. When the user manually click the refresh button again, my event handlers are not been called at all.

     

    My Code to create a querytable:

    public void CreateQueryTableInSheet(string connection, string query, string sheetName)

    {

                Excel._Worksheet sheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.OfType<Excel.Worksheet>().SingleOrDefault(s => s.Name == sheetName);

                Excel.Range range = sheet.get_Range("A1", Missing.Value);

                var listObject = sheet.ListObjects.OfType<Excel.ListObject>().FirstOrDefault(obj => obj.Range.Column == 1 && obj.Range.Row == 1);

     

                if (listObject == null)

                {

                    listObject = sheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcQuery, connection, Missing.Value, Excel.XlYesNoGuess.xlYes, range);

                    listObject.Name = sheetName;

     

                    if (!sheetToQueryStopwatch.ContainsKey(sheetName))

                        sheetToQueryStopwatch.Add(sheetName, new StopwatchQueryTableHelper(sheetName));

     

                    listObject.QueryTable.BeforeRefresh += new Excel.RefreshEvents_BeforeRefreshEventHandler(sheetToQueryStopwatch[sheetName].QueryTable_BeforeRefresh);

                    listObject.QueryTable.AfterRefresh += new Excel.RefreshEvents_AfterRefreshEventHandler(sheetToQueryStopwatch[sheetName].QueryTable_AfterRefresh);

     

                    sheetToQueryStopwatch[sheetName].AfterQueryRefreshEvent += ((querySheetName, queryTime) =>

                    {

                        if (this.AfterQueryRefreshEvent != null)

                            this.AfterQueryRefreshEvent(querySheetName, queryTime);

                    });

     

                    listObject.QueryTable.AfterRefresh += (success =>

                    {

                        if (success)

                        {

                            for (int i = 1; i <= listObject.ListColumns.Count; i++)

                            {

                                var c = listObject.ListColumns[i];

     

                                if (c.Name == "UploadTime")

                                    c.Range.NumberFormat = "m/d/yy h:mm AM/PM;@";

                                else if (c.Name == "TableID" || c.Name == "ActivityID")

                                    c.Range.EntireColumn.Hidden = true;

                            }

                        }

                    });

                }

     

                listObject.QueryTable.CommandType = Excel.XlCmdType.xlCmdSql;

                listObject.QueryTable.CommandText = query;

                listObject.QueryTable.Connection = connection;

                listObject.QueryTable.FillAdjacentFormulas = false;

                listObject.QueryTable.AdjustColumnWidth = true;

                listObject.QueryTable.PreserveColumnInfo = true;

                listObject.QueryTable.PreserveFormatting = true;

                listObject.QueryTable.BackgroundQuery = true;

                listObject.QueryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;

                listObject.QueryTable.RowNumbers = false;

                listObject.QueryTable.SaveData = true;

     

                listObject.QueryTable.Refresh(true);

            }

     

    Helper class for holding the stopwatch timer:

    class StopwatchQueryTableHelper

        {

            public event Action<string, long> AfterQueryRefreshEvent;

     

            Stopwatch stopwatch = new Stopwatch();

            private string sheetName;

     

     

            public StopwatchQueryTableHelper(string sheetName)

            {

                this.sheetName = sheetName;

            }

     

            public void ResetAfterQueryEventHandlers()

            {

                AfterQueryRefreshEvent = null;

            }

     

            public void QueryTable_BeforeRefresh(ref bool Cancel)

            {

                if (!stopwatch.IsRunning)

                    stopwatch.Start();

            }

     

            public long ElapsedTime

            {

                get { return stopwatch.ElapsedMilliseconds; }

            }

     

            public void QueryTable_AfterRefresh(bool success)

            {

                if (stopwatch.IsRunning)

                {

                    stopwatch.Stop();

     

                    if (AfterQueryRefreshEvent != null)

                        AfterQueryRefreshEvent(sheetName, ElapsedTime);

                }

            }

        }

     

     

    The listobject with the querytable are being created on the relevant sheet, and the query table is indeed refreshing the data (I can delete some values from it, and they will be restored if I refresh the table).

    It just seems like the handlers are get lost...

     

     

    Any help / insight will be appreciated.

    Thanks,

    Dror.

     

     

    Monday, September 26, 2011 7:04 AM

Answers

  • Hi Dror,

     

    Thanks for posting in the MSDN Forum.

     

    It’s based on my experience you need set a global variable for your ListObject’s instance. I mean that the variable “listObject” must be a global variable in your snippet, it must be valid through the add-in application to ensure your events will be valid in the hole process of you add-in.

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 2:12 AM
    Moderator