6 Replies Latest reply on Jul 20, 2012 3:42 PM by SDuplaga

    Auto Refresh of smartview data in excel macro

      I wonder whether there is a macro script that enable to refresh smartview data. I just want to refresh data in Smartview through the excel macro script
        • 1. Re: Auto Refresh of smartview data in excel macro
          Antonio R.
          HypRetrieveRange() gives users the ability to refresh a selected or named range of cells in a grid
          or worksheet. If the range provided to this function contains more rows or columns than the
          actual grid has, the additional rows and columns are treated as comments and are thus part of
          the grid.
          Range retrieval clears the Undo buffer, therefore the Undo operation cannot be used afterward.

          Declare Function HypRetrieveRange Lib "HsAddin" (ByVal vtSheetName As
          Variant, ByVal vtRange As Variant, ByVal vtConnName As Variant) As Long
          Worksheets("Sheet2").Names.Add name:="MyRange", RefersTo:="=$E$11:$F$28"
          Sub Sample_RetrieveRange
          Worksheets("Sheet2").Names.Add name:="MyRange", RefersT o:="=$E$11:
          $ F$28"
          sts = HypRetrieveRange("Sheet2", range("E11:F28"), "Samp1")
          'retrieve by regular range
          sts = HypRetrieveRange("Sheet2", range("MyRange"), "Samp1")
          'retrieve by named range
          End sub
          • 2. Re: Auto Refresh of smartview data in excel macro
            I tried your code but i get an error massage says : "Sub or function not defined" for the "HypRetrieveRange" function
            • 3. Re: Auto Refresh of smartview data in excel macro
              Antonio R.
              Did you load the API file in one of the VBA modules? The file is smartview.bas located in \SmartView\bin you must do this to declare all functions or just the function
              you plan to use.
              • 4. Re: Auto Refresh of smartview data in excel macro

                Could you please post the exact procedure as in how to starting from the script to running a macro for the smart view. Also please let me know how do i enable this procedure automatically when the excel is opened.

                This is really very important for me at this point.
                Please help me.

                • 5. Re: Auto Refresh of smartview data in excel macro
                  Did you ever figure out how to Refresh All using a macro? I figured out the VBA code for the macro, and thought others would find this useful. In a new module (preferred), or at the very top of whatever module you are going to place the code, you must insert this declaration. It has to be at the top, or it will not work. Just copy/paste the next row as-is:

                  Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long

                  Now, below the declaration, you have to insert the macro (in the same module):

                  Sub RetrieveData()
                  sts = HypRetrieve("Budget")
                  sts = HypRetrieve("Actuals")

                  End Sub

                  In this macro, I am updating two smartview spreadsheets titled Budget and Actuals. You can update as many worksheets as you want - just copy the line of code: sts = HypRetrieve("Input your worksheet name here") for each additional sheet to be updated. From what I read, if you do not put in a valid sheet name, or leave it blank, it will just update the active sheet.

                  You can also name the macro whatever you want. Mine is named RetrieveData.

                  Good luck!
                  • 6. Re: Auto Refresh of smartview data in excel macro
                    Just wanted to let you know that I followed this set up and everything worked perfectly! Helped me to refresh a large workbook that was failing during a refresh all.

                    I think it would be helpful to add that the tab you are attempting to refresh cannot be hidden. You can use the following to un-hide and then hide a tab:

                    Sheets("Budget").Visible = True
                    [RUN THE FUNCTION]
                    Sheets("Budget").Visible = False

                    Thanks for the assistance!