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)
{
}
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();