3

I use this code to open refresh save and close excel file:

Application excelFile = new Application();               
Workbook theWorkbook = excelFile.Workbooks._Open(Environment.CurrentDirectory + "/WebGate", 0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, System.Reflection.Missing.Value, false);    
Sheets sheets = (Sheets)theWorkbook.Worksheets;    
theWorkbook.RefreshAll();    
theWorkbook.Save();    
excelFile.Quit();

the problem is that there is a conflict between the refresh and save commands because the file is been save while the refresh is been done in the background (I guess) can anyone help me with this? i need to know how can I know when the refresh proccess is done or any other indication that will help my to decide when should I save the file without harm the refresh proccess

Improve this question
2
  • What exactly are the symptoms of this problem? i.e. How do you know about that conflict? Commented Dec 25, 2008 at 14:44
  • pop meesage tell me that the action (save) will harm the refresh wainting refresh proccess and ask me if I want to canceel the action (the save action) Commented Dec 25, 2008 at 15:40

4 Answers 4

Reset to default
4

I am not familiar with C#, but I am good at Excel VBA. The problem here is most of the Pivot tables will have BackgroundQuery property set to True, making the pivot tables refresh asynchronously so that the Excel file remians responsive when used by end user. If you are not adding any new pivot tables during your transaction with the file then you can fix the file once by unchecking the BackgroundQuery in Pivot Table->Table setting->BackgroundQuery under External Data Options. If you are adding a pivot table you need to set this property to false like

Dim oPivot As PivotTable
set oPivot=worksheets("xyz").PivotTables("Pivot1") 
oPivot.PivotCache.BackgroundQuery = False

If you are not sure which table to fix and have lot of tables in your excel then use the below code in Excel VBA to fix it.

Public Sub FixPivotTables()
    Dim oPivot As New PivotTable, oSheet As Worksheet
    For Each oSheet In ThisWorkbook.Worksheets
        For Each oPivot In oSheet.PivotTables
            oPivot.PivotCache.BackgroundQuery = False
        Next
    Next
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Again, I have looked at the documentation and here is what I can say.

Get hold of the QueryTable object you are refreshing. It has an event named "AfterRefresh" which you can use to take any action.

Also, instead of doing Refresh on workbook, do a Refresh on the specific QueryTable (unless you have multiple QueryTables). The QueryTable has a Refresh method, which takes a boolean parameter named BackGroundQuery, which you can set to False.

I guess, this will query the records synchronously.
Does that work for you?

Improve this answer

Comments

2

Eran, I am posting this based on what I understand of documentation.

I assume you are using pivottables & it uses some kind of query.
Try using Workbook object's PivotTableCloseConnection & see if you can call Save inside it.

In Excel 2007, Application object has AfterCalculate event - which could also be of use.

How does one simulate this scenario?

Improve this answer

Comments

0

Try to use:

this.Application.ActiveWorkbook.RefreshAll();
this.Application.ActiveWorkbook.Save();

If necessary use the .ReCalculate() action.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.