none
C# refresh Excel RRS feed

  • Question

  • I'm using C# coding to auto refresher Excel. I saw someone set BackgroundQuery the to false like following, would you like me konw why?

    1. foreach (xls.WorkbookConnection wc in eBook.Connections)
    2.                 {
    3.                     if (wc.Type.ToString() == "xlConnectionTypeODBC")
    4.                     {
    5.                         wc.ODBCConnection.BackgroundQuery = false;
    6.                     }
    7.                     else
    8.                     {
    9.                         wc.OLEDBConnection.BackgroundQuery = false;
    10.                     }
    11.                 }

    Thanks!

    • Changed type SSAS_user Thursday, October 27, 2011 1:08 PM
    • Changed type SSAS_user Thursday, October 27, 2011 1:09 PM question
    Thursday, October 27, 2011 1:08 PM

Answers

  • Erros that start with 0x8 are priveledge errors, memory pointers not valid, or files that don't exist.  The connection may not be valid or you can't delete the conection because the server is busy (may be doesn't exist).

     

    I would wait until you get an error then add the variable "wc" to a watch window by highlighting the variable with the mouse and right click over the highlighted variable.  then press the plus sign in the watch window.  It will give you clues to the problem.


    jdweng
    Friday, October 28, 2011 10:14 AM

All replies

  • there could be lots of logical answers.  Each application may be different and it is up to the individual is there want background updating on or off.  There is also another option that updates queries when a workbook is opened.

     

    Queries will take time to execute and can slow down other operations in the workbook.  The user may have a macro that moves data around on the worksheet after the query completes and doesn't want the query updated after the data has been moved.


    jdweng
    Thursday, October 27, 2011 2:19 PM
  • I use code like that and it was mainly because the application would move on to the very next line of code, after Excel started the background refresh of the data.  This tended to break the automated process because the file was still open and in use, but then the application was trying to do other things with it.  So, by turning off the background refresh, Excel would keep the focus and the C# app wouldn't think that Excel had completed the task, until it was actually complete.  My code could then complete the other tasks without any exceptions being thrown.
    Thursday, October 27, 2011 9:23 PM
  • Thanks.

    try
    
    {
    
    //Setting to false to return control only after all data has been fetched to the worksheet
    
    foreach (WorkbookConnection wc in eBook.Connections)
    
    {
    
    if (wc.Ranges.Count > 0)
    
    {
    
    switch (wc.Type)
    
    {
    
    case XlConnectionType.xlConnectionTypeODBC:
    
    ValidateOdbcConnection(wc.ODBCConnection.Connection);
    
    wc.ODBCConnection.BackgroundQuery = false;
    
    break;
    
    case XlConnectionType.xlConnectionTypeOLEDB:
    
    ValidateOledbConnection(wc.OLEDBConnection.Connection);
    
    break;
    
    }
    
    }
    
    else
    
    {
    
    wc.Delete();
    
    }
    
    }
    
    }
    
    

    I'm using above code but sometimes it return error like this:

    The

    message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

     

    Do you kown what's the root cause of this error?

     

    Thanks,

    Raymond
     

    Friday, October 28, 2011 6:10 AM
  • Erros that start with 0x8 are priveledge errors, memory pointers not valid, or files that don't exist.  The connection may not be valid or you can't delete the conection because the server is busy (may be doesn't exist).

     

    I would wait until you get an error then add the variable "wc" to a watch window by highlighting the variable with the mouse and right click over the highlighted variable.  then press the plus sign in the watch window.  It will give you clues to the problem.


    jdweng
    Friday, October 28, 2011 10:14 AM