current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question
    
What exactly are the symptoms of this problem? i.e. How do you know about that conflict? –  configurator Dec 25 '08 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) –  eran Dec 25 '08 at 15:40
add comment

4 Answers

up vote 2 down vote

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?

share|improve this answer
add comment
up vote 2 down vote

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?

share|improve this answer
add comment
up vote 3 down vote

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
share|improve this answer
add comment
up vote 0 down vote

Try to use:

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

If necessary use the .ReCalculate() action.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.