locked
Scheduling the refresh of data in Excel using Data Connection Libraries RRS feed

  • Question

  • Hi All,

    I have several excel 2007 files stored within a SharePoint 2007 site which I manually update each morning. Basically I extract data from a TeraData warehouse, refresh a hidden worksheet and then refresh a pivot table. I'm now looking at centralising these connections within a Data Connection library and then scheduling the refresh of these connections. I was able to find this guide detailing how a Data Connection can be setup and managed securely. The only issue is that I'm not sure how I can schedule the refresh? Also, when those connections are actually refreshed, how is error handling dealt with?

    Obviously this kind of scenario is more suited to the use of a BI Tool such as SQL Reporting Services however I don't have any funding for this kind of software so I'm more interested in how I can achieve this level of automation with the software that I have.

    • Moved by Mike Walsh FIN Wednesday, March 23, 2011 11:32 AM excel files in a SP site does not make a question an Excel Services question (From:SharePoint - Excel Services (pre-SharePoint 2010))
    Wednesday, March 23, 2011 11:00 AM

Answers

  • Paul,

    The only way that you'll be able to achieve this I think is via a timer job.  Have you got access to any development resource?

    A good starter link is this one, from Andrew Connell's blog: http://www.andrewconnell.com/blog/archive/2007/01/10/5704.aspx

     


    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    • Marked as answer by Peng Lei Tuesday, March 29, 2011 8:54 AM
    Wednesday, March 23, 2011 4:45 PM
    Answerer

All replies

  • Also, please note that I am aware of the Automatic refresh (periodic / on-open) options however the periodic option seems to be convoluted given that I would prefer to link the extration of each query/excel refresh so that the 'batch' runs as fast as possible.  
    Wednesday, March 23, 2011 12:12 PM
  • Can ayone advise?
    Wednesday, March 23, 2011 4:34 PM
  • Paul,

    The only way that you'll be able to achieve this I think is via a timer job.  Have you got access to any development resource?

    A good starter link is this one, from Andrew Connell's blog: http://www.andrewconnell.com/blog/archive/2007/01/10/5704.aspx

     


    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    • Marked as answer by Peng Lei Tuesday, March 29, 2011 8:54 AM
    Wednesday, March 23, 2011 4:45 PM
    Answerer
  • Thanks Steven for coming back to me. I really wanted to minimise the amount of custom development for this project. I was thinking that I could possibly push some of the complexity back on the database, dumping the outputs of my queries into tables. However I would still need some sort of trigger to refresh the Excel Workbooks on SharePoint. I could set the Workbooks to refresh on open, however I want to avoid having to avoid having to pull back the same data many times.  
    Thursday, March 24, 2011 2:02 PM
  • So I have been looking into these Timer Jobs in more detail (in the hope that I could find something standard) and I managed to find this: http://msdn.microsoft.com/en-us/library/ee210595.aspx

    Basically PowerPivot comes with a Standard Excel Refresh Timer Job that I belive is installed with PowerPivot. With that in mind, is there any way we can leverage this functionality? I'll keep looking but any advice would be much apreciated.

    Thursday, March 24, 2011 3:25 PM
  • My understanding of PowerPivot for SharePoint was that it was 2010, not 2007.  I'll double check but I don't think that this is going to be an option for you.
    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    Thursday, March 24, 2011 4:19 PM
    Answerer
  • I think you are correct. I guess I was just interested in whether there was a standard timer job which we could leverage.
    Thursday, March 24, 2011 4:36 PM