locked
Thread.Sleep Alternative RRS feed

  • Question

  • I am using C# to open and refresh a few Excel workbooks.  Some of them are very large workbooks and take quite some time to refresh.  In order to prevent the syntax from closing the workbook before it is completely refreshed I have implemented this syntax

    oWB.RefreshAll();
    Thread.Sleep(1000000);

    But sometimes that is overkill as that waits a very long time.  Other times believe it or not, it is not enough time.  Is there a better alternative to delaying the next line of code from running until the previous line of code has fully executed?

    • Moved by CoolDadTx Monday, September 15, 2014 3:54 PM Office related
    Monday, September 15, 2014 1:31 PM

Answers

  • Hi Montoya,

    Please try:

    foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets)
                {
                    foreach (Microsoft.Office.Interop.Excel.ListObject table in worksheet.ListObjects)
                    {
                        try
                        {
                            table.QueryTable.BackgroundQuery = false;
                        }
                        catch (Exception ex)
                        {
                            
                        }
                    }
                }

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by IndigoMontoya Tuesday, September 16, 2014 1:22 PM
    Tuesday, September 16, 2014 6:31 AM

All replies

  • Hello,

    have a look at [1] - if you set the "BackgroundQuery" property of all tables in all worksheets to "false", "workbook.RefreshAll" should block until the operation is finished:

    foreach (Worksheet worksheet in workbook.Worksheets)
    {
        foreach (QueryTable table in worksheet.QueryTables)
            table.BackgroundQuery = false;
    }
    
    //This will finish executing before moving on
    workbook.RefreshAll();

    [1] http://stackoverflow.com/questions/18788384/wait-for-workbook-refreshall-c

    Regards,
    Henning Dieterichs
    Developer-Hotline for MSDN Online Germany

    Disclaimer:
    Please take into consideration, that further inquiries cannot or will be answered with delay.
    For further information please contact us per telephone through the MSDN-Entwickler-Hotline: http://www.msdn-online.de/Hotline
    MSDN-Entwickler-Hotline: Fast and professional help for software developers free of charge!

    For this post by the MSDN-Entwickler-Hotline the following terms and conditions apply: Trademarks, Privacy as well as the separate terms of use for the MSDN-Entwickler-Hotline .

    • Marked as answer by IndigoMontoya Monday, September 15, 2014 2:37 PM
    • Unmarked as answer by IndigoMontoya Monday, September 15, 2014 2:57 PM
    Monday, September 15, 2014 1:45 PM
  • According to this you should set BackGroundQuery property to false to hang application until workbook is refreshed.
    Monday, September 15, 2014 1:51 PM
  • Hello,

    have a look at [1] - if you set the "BackgroundQuery" property of all tables in all worksheets to "false", "workbook.RefreshAll" should block until the operation is finished:

    foreach (Worksheet worksheet in workbook.Worksheets)
    {
        foreach (QueryTable table in worksheet.QueryTables)
            table.BackgroundQuery = false;
    }
    
    //This will finish executing before moving on
    workbook.RefreshAll();

    [1] http://stackoverflow.com/questions/18788384/wait-for-workbook-refreshall-c

    Regards,
    Henning Dieterichs
    Developer-Hotline for MSDN Online Germany

    Disclaimer:
    Please take into consideration, that further inquiries cannot or will be answered with delay.
    For further information please contact us per telephone through the MSDN-Entwickler-Hotline: http://www.msdn-online.de/Hotline
    MSDN-Entwickler-Hotline: Fast and professional help for software developers free of charge!

    For this post by the MSDN-Entwickler-Hotline the following terms and conditions apply: Trademarks, Privacy as well as the separate terms of use for the MSDN-Entwickler-Hotline .

    This seems to only work (at least for me) in workbooks that were created in Excel 2000.  If the query was created in Excel 2007 or 2010 - this does not work.

    EDIT ---

    Also if I run this VBA script in the same workbook (whether it be Excel 2007 or Excel 2010) It does not show ANY querytables in the workbook

    Sub ListQueryTables()
    Dim i As Integer, ws As Worksheet
    Set ws = Sheets("Sheet1")
    For i = 1 To ws.QueryTables.Count
       MsgBox ws.QueryTables(i).Destination.Address
    Next i
    End Sub


    EDIT # 2 -----------

    If I run this VBA it will show me the connection names -- still not sure how to do the above though..

    Sub ConnNames()
    Dim conn As WorkbookConnection
    For Each conn In ActiveWorkbook.Connections
      Debug.Print conn.Name
    Next conn
    End Sub

    Edit # 3 ---------------

    After continuing with the VBA route I think that if I can get this interpreted into C# syntax this should do the trick

    foreach (Excel.Worksheet worksheets in workbook.Worksheets)
    {
      foreach (Excel.ListObject.QueryTable qt in worksheet.ListObjects)
      {
        qt.BackgroundQuery = false;
      }
    }

    But I get a compile error of Excel.ListObejct.QueryTable is a 'property' but is used like a 'type'

    Monday, September 15, 2014 2:57 PM
  • Hi Montoya,

    Please try:

    foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets)
                {
                    foreach (Microsoft.Office.Interop.Excel.ListObject table in worksheet.ListObjects)
                    {
                        try
                        {
                            table.QueryTable.BackgroundQuery = false;
                        }
                        catch (Exception ex)
                        {
                            
                        }
                    }
                }

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by IndigoMontoya Tuesday, September 16, 2014 1:22 PM
    Tuesday, September 16, 2014 6:31 AM