CODEWITHSUNDEEP
c#multithreadingbackgroundworker
Big Pimpin
Big Pimpin

Reputation: 437

Application.Wait or Thread.Sleep

I am already using backgroundworker.RunAsyn() to run my code on a separate thread. However I am hitting a portion where the code iterates to the next line before the previous line is completed. Should I create a separate backgroundworker to handle that? Or should I use Application.Wait() or Thread.Sleep() I am not sure the amount of time to delay and I'd rather not have my program just sitting around waiting for extra un-needed time so I am not sure which route to take. Here is a snippet of the trouble-maker.

public Form_Main()
{
   InitializeComponent();
   backgroundworker1.WorkerReportsProgress = true;
   backgroundworker1.DoWork += new DoWorkEventHandler(backgroundWorker1_DoWork);
   backgroundWorker1_ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged);
}

private void btnOpenRefreshSave_Click()
{
   backgroundWorker1_RunWorkerAsync();
}

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
   Excel.Application exApp;
   Excel._Workbook exBook;
   Excel._Worksheet exSheet;

   exBook = (Excel._Workbook)(exApp.WOrkbooks.Open("C:\\Book1.xlsx")); 
   exSheet = (Excel._Worksheet)(exBook.ActiveSheet);
   //This is the line of code that often times takes a while
   exBook.RefreshAll();
   //end of trouble line
   exBook.SaveAs("C:\\Updated_Book1.xlsx");
}

private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{

}

Upvotes: 1

Views: 556

Answers (3)

Bob Goblin
Bob Goblin

Reputation: 1269

A few things come to mind on what to do here. You could try using something similar to the below

if (Application.CalculationState === xlDone Then
    everything is finished calculating[enter link description here][1]

Another option would be (as others have suggested) changing the background refresh property. A quick scan of the workbooks could programmatically change that for you

foreach (Wrksheet ws in workbook.wss)
{
    foreach (QueryTable table in ws.QueryTables)
      table.BackgroundQuery = false;
}
workbook.RefreshAll();

Upvotes: 2

Philip Stuyck
Philip Stuyck

Reputation: 7467

The problem is caused because RefreshAll is running on a background thread. So basically you have your own backgroundworker running and another one you did not anticipate for.

The documentation for refreshAll says : Objects that have the BackgroundQuery property set to true are refreshed in the background.

So you can get out of this problem only be setting that property to false. Then the refreshall would run in the context of your backgroundworker which is what your intent is.

If this still does not work, then you have to rethink your logic and look for an event of some kind that is triggered when the refresh is done. If this does not exist, then there is no solution other than a sleep, but that is not a good solution at all because you don't know how long to sleep.

Upvotes: 2

Mohsen
Mohsen

Reputation: 79

Why do you want to delay something, can't you do saving your workbook on one of its events like SheetCalculate (Occurs after any worksheet is recalculated or after any changed data is plotted on a chart) and setting some flag in your code and reset that on that event (or any more relevant event)

Upvotes: 0

Related Questions