refresh and save excel file via c#

Question

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

Answer 1

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?

Answer 2

thank you guys for your help
I'm sorry I guess my question wasn't clear enough

I use excel web query in order to read data from the web.
the data that I read from the excel file I write to SQl tables and use them in my program.

so -
1. when I want new data I refresh the file using RefreshAll()
2. i write few lines to the excel file ( those line are not in the code above but they
are in the code i can attch them if it be useful) to fit the excel headers to the sql table hedaers
3. save the file
4. i read the fresh data into data table
5. insert the data table into my sql table

my problem is that RefreshAll() is void method and i don't know when it done so when the code reach section 3 I receive pop up message telling me that the save
action conflict the refresh action and ask me if I want to quit the saving.
i tried to use Thread.Sleep(5000) in order to cause the program to wait to the excel refresh it not allways works(odd times only)
I hope i was clear enough this time please let me know if you need any more details

THANKS Eran

Answer 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?

Answer 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

Answer 5

Hi,
I have looked for more information on the AfterRefresh event which seems to be very helpfull to me but I didnt find anything usefull on it. I tried to use the QueryTables refresh but again the save was done before the refresh was end (I used the BackgroundQuery as false) can you please link me to any information or articales about the events issue?
thanks again
Eran

Answer 6

Hi guys , I wanted to thank you all a lot for the help
I succeed to use refresh in the query tables and it was just what I have looking for THANKS A LOT Eran

Answer 7

Eran,

You did an excellent job.

I got as same as your problem. Could you tell me how to use the refresh in the query tables to fix the problem? Thank you very much.

Wang