none
Automatic Refresh of Excel Reports RRS feed

  • Question

  • I have a number of Excel Reports / Dashboards which require refreshing daily after the main SQL server table from which the data is taken from, gets updated.

    I had considered using a SSIS package to loop over the files but was wondering if there was a more efficient way to undertake this process and automate refreshing of the reports ?

    Thursday, September 3, 2015 3:48 PM

Answers

  • Hi scott,

    Based on my understanding, it depends how you developed the reports. If you connect the data via the connection, we can config the connection to referesh at intervals like figure bleow:

    In addition, we can use code below to refresn the query table in slection and put the code where you want to referesh. In addition, we can use Record Macro to get a quick start for the sample code:

     Selection.ListObject.QueryTable.Refresh 

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 4, 2015 2:10 AM
    Moderator
  • Hi Scott,

    >>We only want the reports to be refreshed once per day (every 1440 minutes) however there seems to be a limit of 999 minutes in the connection properties, therefore it seems that the optimum solution would be to refresh twice per day (every 720 mins) however one of these refreshes would then be superfluous.<<

    Based on my understanding, if the refresh interval is so long, we can refresh the data when we open the workbook.

    And also we can call the procedure at sepcific time by using Appliation.OnTime. And here is an example that refresh the listobject once per day. You can run this method in workbook open event:

    Sub RefreshList()
    
    Application.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    Application.Application.OnTime Now + TimeValue("23:59:59")+TimeValue("00:00:01"), "RefreshList"
    
    End Sub
    >>Ideally, we would want to link the refreshes to the data source (SQL server table) being updated - is there a specific way we could do this so that a refresh is only triggered upon the source being updated ?<<

    As far as I know, there is no such method we can refresh based on whether the data source was updated. Because, we are not able to know the data is updated in Excel unless we get the data from data source and comapre it with old data.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, September 8, 2015 8:07 AM
    Moderator

All replies

  • Hi scott,

    Based on my understanding, it depends how you developed the reports. If you connect the data via the connection, we can config the connection to referesh at intervals like figure bleow:

    In addition, we can use code below to refresn the query table in slection and put the code where you want to referesh. In addition, we can use Record Macro to get a quick start for the sample code:

     Selection.ListObject.QueryTable.Refresh 

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 4, 2015 2:10 AM
    Moderator
  • Hi Fei,

    We only want the reports to be refreshed once per day (every 1440 minutes) however there seems to be a limit of 999 minutes in the connection properties, therefore it seems that the optimum solution would be to refresh twice per day (every 720 mins) however one of these refreshes would then be superfluous.

    Ideally, we would want to link the refreshes to the data source (SQL server table) being updated - is there a specific way we could do this so that a refresh is only triggered upon the source being updated ?

    thanks

    Scott

    Monday, September 7, 2015 3:09 PM
  • Hi Scott,

    >>We only want the reports to be refreshed once per day (every 1440 minutes) however there seems to be a limit of 999 minutes in the connection properties, therefore it seems that the optimum solution would be to refresh twice per day (every 720 mins) however one of these refreshes would then be superfluous.<<

    Based on my understanding, if the refresh interval is so long, we can refresh the data when we open the workbook.

    And also we can call the procedure at sepcific time by using Appliation.OnTime. And here is an example that refresh the listobject once per day. You can run this method in workbook open event:

    Sub RefreshList()
    
    Application.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    Application.Application.OnTime Now + TimeValue("23:59:59")+TimeValue("00:00:01"), "RefreshList"
    
    End Sub
    >>Ideally, we would want to link the refreshes to the data source (SQL server table) being updated - is there a specific way we could do this so that a refresh is only triggered upon the source being updated ?<<

    As far as I know, there is no such method we can refresh based on whether the data source was updated. Because, we are not able to know the data is updated in Excel unless we get the data from data source and comapre it with old data.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, September 8, 2015 8:07 AM
    Moderator