Refresh an Excel workbook that uses external data from an Oracle database without user intervention

Question

I created an excel file that has an external connection to an Oracle database. This excel workbook has a sheet with a pivot table that allows others to extract a wealth of information/meaning from the data.

We do not want to install the Oracle client on every users' computer who wants to use this excel workbook so we split the excel workbook into two files. One with the external data and another with just the pivot table, which references the other excel workbook with the data.

We need the data in the excel workbook with the data to refresh periodically without having someone manually open the excel workbook with the data every time it needs to be refreshed. Any suggestions on the best way to go about this?

e.g., maybe run a script called by Windows task scheduler that will have the datasource update?

Thanks.

Excel can be a reporting solution but is not optimum. SQL Server reporting solutions (SSRS) can do this easily and with a single report as opposed to multiple spreadsheets copied to every user's computer. — kevinskio
The spreadsheets are on on a network share. I will look into SSRS as an option. — jaromey
Is SSRS similar to Crystal Reports? I ask because I know the useres will still want to use Excel because they are comfortable with it and can manipulate the pivot tables to quickly find what they want -- as opposed to a static report. Essentially, I am saying that whatever solution SSRS provides, the users will still need the data accessible in Excel with a pivot table. — jaromey
With SSRS it can connect to many different types of databases. You can deploy a report to a server and have it email an excel report to users or they can go to a web site,click on the report and then export to excel. — kevinskio
OK. Thanks @kevinsky . Looks like I have some more research on my hands to do. If you have any good resources that I could take a look at, I would appreciate if you could point me to it. — jaromey

jaromey jaromey · Accepted Answer · 2015-10-08T18:11:14

In the comments, using SQL Server Reporting Services was suggested as an option. I started doing some research on that as an option but I need a really quick solution so I ended up doing the following:

I managed to find some posts online with some code that lead me to a solution. http://www.mattmasson.com/2013/03/refresh-an-excel-workbook-with-a-script-task/

and

https://social.msdn.microsoft.com/Forums/vstudio/en-US/8bce17fb-eacd-4df3-8c10-bf8ae8a93c55/c-code-to-refresh-excel-data?forum=csharpgeneral

The code in both are very similar. It is written in c# and looks something like this:

        var filename = "SomePath\\Book2.xlsx";
        object missingValue = System.Reflection.Missing.Value;
        var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        excelApp.DisplayAlerts = false;
        var Workbook = excelApp.Workbooks.Open(filename, missingValue, missingValue,
            missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue,
            missingValue, missingValue, missingValue, missingValue, missingValue);
        Workbook.RefreshAll();
        System.Threading.Thread.Sleep(30000);

        Workbook.Save();
        Workbook.Close(false, filename, null);
        excelApp.Quit();
        Workbook = null;

        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

The call to Thread.Sleep() ensures that the data gets refreshed before the Workbook is saved and closed. If you call Workbook.Save() before the refresh is complete, then the data does not get updated. There has to be a better way to do this, but I don't know what it is yet.

I am just using Windows task scheduler to call this script to run at specific times. It is not the most elegant solution but it works.

Refresh an Excel workbook that uses external data from an Oracle database without user intervention

1 Answers