none
Refresh Partial Sections in the Excel. RRS feed

  • Question

  • I have huge excel reports which are connected to the SSAS tabular cube. These excels fetch data using CubeValue functions. Consider there are 1000 cells fetching the data in this sheet. We have written a VBA macro provide a button and refresh the data connections to refresh all the cells. Now it takes 3-4 minutes to refresh the excel and in the mean time it  goes in the hanging state.

    This is a very bad experience for my end users.

    I am looking for an option to do a partial refresh if at all possible. So that my user can see lets assume 250 cells refreshed and can start working on the things.

    Can we achieve this functionality without excel being in the frozen state.


    ashu

    Friday, July 7, 2017 7:28 PM

All replies

  • I'm afraid it would be impossible to refresh partial sections in Excel.

    It hit on me that we could Hide/Unhide Rows/Columns for showing partial section. 
    If cells are allocated vertically, Hide/Unhide Rows in some interval (e.g. every 10 times of fetching data) and while fetching 1-9, stop ScreenUpdating.

    coding image:
     

    Ashidacchi

    Saturday, July 8, 2017 2:58 AM
  • Hi Ashwani26,

    you had mentioned that,"These excels fetch data using CubeValue functions. We have written a VBA macro provide a button and refresh the data connections to refresh all the cells."

    you did not posted the code so we don't know how you exactly refreshing the data.

    you can try to use code like below, in which you can refresh certain range or selected part of the sheet.

    Public Sub RecalculateSelection()
        If TypeName(Selection) = "Range" Then Selection.Calculate
    End Sub
    

    this is just an example, you need to implement your logic according to your requirement.

    Reference:

    Range.Calculate Method (Excel)

    further you can visit link below that may help you.

    Refresh an external data connection

    Refresh data connected to another workbook

    you can also try to change the connection properties and try to provide the range in that.

    then you can try to fire multiple queries and run the process in background.

    try to make a test with options above and let us know if that work for you or not.

    if that not work for you then try to provide the code you are using also it is better if you provide the sample Excel file with dummy data that can reproduce the issue on our side.

    so that we can make a test with it and try to provide you further suggestion to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 10, 2017 1:02 AM
    Moderator
  • Hi ashu,

    >> So that my user can see lets assume 250 cells refreshed and can start working on the things.

    Do you mean refresh 250 cells of 1000 under the same connection? If so, it is impossible.

    For a workaround to avoid Excel application freeze, I would suggest you try refresh BackgroundQuery.

    Here is a simple code:

        Application.ScreenUpdating = False
        DoEvents
        ActiveWorkbook.RefreshAll    
        Application.ScreenUpdating = True
    

    Best Regards,

    Edward 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 14, 2017 9:11 AM
  • Hi Ashwani26,

    I can see that after creating this thread , you did not follow up this thread again.

    this thread is still open.

    if you find the solution by your self then I post the solution and mark it as an answer.

    if the issue is still exist then refer the suggestion given by the community member. that suggestion can solve your issue.

    if you have any further question regarding this issue then let us know about that. we will try to provide you further suggestions.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 7, 2017 9:33 AM
    Moderator