VBA et SmartView - Fonction HsGetValue

J’ai réalisé un code qui me permet de de mettre à jour les valeurs de la fonction SmartView HsGetValue avant de rafraichir et de remplacer les formules par leurs valeurs, le code répète l’action pour toutes les colonnes qui ont été ajoutées dans la feuille, i.e. :

Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long
Declare Function HypMenuVRefresh Lib "HsAddin.dll" () As Long

Sub SV_Retrieve()
  x = HypMenuVRefresh()
End Sub

Sub Retrieve_values()

Dim i, n, l As Integer

'--------------------------------STEP1: loop to set formulas-----------------------
For l = 7 To 300

    If Cells(1, l) <> 0 Then

        For i = 12 To 300

            '  Scenario
            VAR1 = Cells(1, l).Address
            VAR2 = Cells(2, l).Address
            VAR3 = Cells(3, l).Address
            VAR4 = Cells(4, l).Address
            VAR5 = Cells(5, l).Address
            VAR6 = Cells(6, l).Address

                    ' currency, custom7 etc...
                    VAR7 = "$B$1"
                    VAR8 = "$B$2"
                    VAR9 = "$B$3"
                    VAR10 = "$B$4"

                        ' Customs
                        CUSTOM1 = "$A" & i
                        CUSTOM2 = "$B" & i
                        CUSTOM3 = "$C" & i
                        CUSTOM4 = "$D" & i
                        CUSTOM5 = "$E" & i
                        CUSTOM6 = "$F" & i

                             If Cells(i, 6) <> 0 Then

                ' To set the value of customs to the corresponding line, CUSTOM5 = ICP

                                Cells(i, l).FormulaR1C1 = "'=HsGetValue(""Hyperion"";""Scenario#"" & " & VAR1 & " & "";Year#"" & " & VAR2 & " & "";Period#"" & " & VAR3 & _
                                " & "";View#"" & " & VAR4 & " & "";Entity#"" & " & VAR5 & " & "";Value#"" & " & VAR6 & " & "";Account#"" & " & CUSTOM6 & " & "";ICP#"" &  " & CUSTOM5 & _
                                " & "";Custom1#"" & " & CUSTOM1 & " & "";Custom2#"" & " & CUSTOM2 & " & "";gaap#"" & " & CUSTOM3 & " & "";nature#"" & " & CUSTOM4 & _
                                " & "";Restatement#"" & " & VAR7 & " & "";Disposals#"" & " & VAR8 & " & "";Custom7#"" & " & VAR9 & " & "";Currency#"" & " & VAR10 & " & """")/1000000"

                            Cells(i, l).Caption = Replace(Cells(i, l), "'=HsGetValue", "=HsGetValue")

                        End If

            Next i

    End If

Next l

'--------------------------------STEP2: Refresh of values--------------------------------

SV_Retrieve

'--------------------------------STEP3: Replace formulas by values---------------------------

For n = 12 To 300

    If Cells(n, 6) <> "" Then

        Cells(n, l) = Cells(n, l).Value

    End If

Next n

End Sub

'-----------------------------------END-----------------------------------

On obtient donc la formule au format texte. Mon problème réside dans le fait que je n’arrive pas à remplacer l’apostrophe par rien et donc à lancer la formule, que ce soit directement dans Excel avec Ctrl + H ou en VBA avec le code suivant :

Cells(i, l).Caption = Replace(Cells(i, l), "'=HsGetValue", "=HsGetValue")

Quand je lance le code de cette manière j’ai l’erreur suivante  « Run-time error ‘438’ : Object doesn’t support this property or method ».

C’est un peu barbare mais c’est la seule solution que j’ai trouvé pour le moment. Est-ce que quelqu’un une idée ?

Merci d’avance à ceux qui prendront le temps de me lire

Jeremy

je viens de faire un test avec le code suivant, mais pas plus de résultat:

For l = 7 To 300

    If Cells(1, l) <> 0 Then

            For i = 12 To 300

            '  Scenario
            VAR1 = CStr(Cells(1, l).Address)
            VAR2 = CStr(Cells(1, l).Address)
            VAR3 = CStr(Cells(1, l).Address)
            VAR4 = CStr(Cells(1, l).Address)
            VAR5 = CStr(Cells(1, l).Address)
            VAR6 = CStr(Cells(1, l).Address)

                        ' Customs
                        CUSTOM1 = "$A" & i
                        CUSTOM2 = "$B" & i
                        CUSTOM3 = "$C" & i
                        CUSTOM4 = "$D" & i
                        CUSTOM5 = "$E" & i
                        CUSTOM6 = "$F" & i

                        If Cells(i, 6) <> 0 Then

                                ' To set the value of customs to the corresponding line

                            Cells(i, l).FormulaR1C1 = "'=HsGetValue(""Hyperion"",""Scenario#"" & H$1 & "";Year#"" & H$2 & "";Period#"" & H$3 & "";View#"" & H$4 & "";Entity#"" & H$5 & "";Value#"" & H$6 & "";Account#"" & CUSTOM6 & "";ICP#"" &  CUSTOM5 & "";Custom1#"" & CUSTOM1 & "";Custom2#"" & CUSTOM2 & "";gaap#"" & CUSTOM3 & "";nature#"" & CUSTOM4 & "";Restatement#"" & $B$1 & "";Disposals#"" & $B$2 & "";Custom7#"" & $B$3 & "";Currency#"" & $B$4 & """")/1000000"

                            ' Replace values to the corresponding CUSTOM (Col)  and corresponding line with CUSTOM5 = ICP and CUSTOM6 = ACCOUNT

                            Cells(i, l).Value = Replace(Replace(Replace(Replace(Replace(Replace(Cells(i, l).Value, "CUSTOM1", "A$" & i), "CUSTOM2", "B$" & i), "CUSTOM3", "C$" & i), "CUSTOM4", "D$" & i), "CUSTOM5", "E$" & i), "CUSTOM6", "F$" & i)
                            Cells(i, l).Value = Replace(Replace(Replace(Replace(Replace(Replace(Cells(i, l).Value, "CUSTOM1", VAR1), "CUSTOM2", VAR2), "CUSTOM3", VAR3), "CUSTOM4", VAR4), "CUSTOM5", VAR5), "CUSTOM6", VAR6)

                        End If
            Next i

    End If

Next l

Cette fois j'ai un Typemysmatch sur:

Cells(i, l).Value = Replace(Replace(Replace(Replace(Replace(Replace(Cells(i, l).Value, "CUSTOM1", VAR1), "CUSTOM2", VAR2), "CUSTOM3", VAR3), "CUSTOM4", VAR4), "CUSTOM5", VAR5), "CUSTOM6", VAR6)

Bonjour jeremyc,

En préfixant d'un tilde ~ , cela devrait le faire

klin89