1

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)
{

}
Big Pimpin
  • 427
  • 9
  • 23
  • What do you mean by "the code iterates to the next line before the previous line is completed"? – Sriram Sakthivel Apr 25 '15 at 19:23
  • @SriramSakthivel it will save the workbook before the Refresh operation has had time to complete. – Big Pimpin Apr 25 '15 at 19:24
  • Refer [this](http://stackoverflow.com/questions/8925403/excel-vba-refresh-wait) and [this](http://stackoverflow.com/questions/18788384/wait-for-workbook-refreshall-c) – Sriram Sakthivel Apr 25 '15 at 19:32
  • Is there anything you want to do between the *trouble-maker* and the next line? – Adriano Repetti Apr 25 '15 at 19:33
  • @AdrianoRepetti - No, I just want to ensure that the trouble-maker line completely executes before moving on. – Big Pimpin Apr 25 '15 at 19:34
  • What part of the code is running async ? – Philip Stuyck Apr 25 '15 at 19:35
  • 1
    RefreshAll is synchronous so there isn't anything else you have to do... – Adriano Repetti Apr 25 '15 at 19:36
  • @PhilipStuyck see my edit to show full code. – Big Pimpin Apr 25 '15 at 19:39
  • @AdrianoRepetti their is somethign else I have to do as the code set as it currently is saves and closes the workbook before a full refresh has time to complete. – Big Pimpin Apr 25 '15 at 19:40
  • 3
    @AdrianoRepetti Documentation [says something different](https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.refreshall.aspx). Am I right about the doc? – Sriram Sakthivel Apr 25 '15 at 19:40
  • Everything in dowork is running on the same thread. How can refreshall not be complete before save ? If refreshall is really async, then this is not really a problem of your backgroundworker,(it would also be a problem in a single threaded application I mean) and you need to find a way to find out when refreshall is complete. – Philip Stuyck Apr 25 '15 at 19:47
  • Objects that have the BackgroundQuery property set to true are refreshed in the background. can you set it to false ? – Philip Stuyck Apr 25 '15 at 19:52
  • @PhilipStuyck Unless I am able to somehow iterate each query and change that property after the WorkbookOpenEvent, unfortunately not. – Big Pimpin Apr 25 '15 at 19:54

3 Answers3

2

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.

Philip Stuyck
  • 7,344
  • 3
  • 28
  • 39
  • It's unrealistic for me to be able to alter everything to enablebackgroundrefresh = false. So I think I will be stuck having to use Thread.Sleep() as much as I despise it. – Big Pimpin Apr 25 '15 at 20:17
2

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();
Bob Goblin
  • 1,251
  • 3
  • 16
  • 33
0

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)

Mohsen
  • 79
  • 5