This content has been marked as final. Show 6 replies
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
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"
Worksheets("Sheet2").Names.Add name:="MyRange", RefersT o:="=$E$11:
sts = HypRetrieveRange("Sheet2", range("E11:F28"), "Samp1")
'retrieve by regular range
sts = HypRetrieveRange("Sheet2", range("MyRange"), "Samp1")
'retrieve by named range
I tried your code but i get an error massage says : "Sub or function not defined" for the "HypRetrieveRange" function
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.
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.
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):
sts = HypRetrieve("Budget")
sts = HypRetrieve("Actuals")
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.
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!