Formule matricielle lancée depuis VBA access vers excel

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,

Je bloque depuis quelques heures sur une incompatibilité de type et je n'arrive pas à trouver l'erreur.

J'ai une base access. Je pilote un fichier excel depuis access. Mon but est de faire des calculs (combinaison index et equiv multicritères) dans le fichier excel en le déclenchant d'access.

Dans excel directement, mes formules fonctionnent très bien (voir pièce jointe). Dans access, ça plante et je vous mets ci-dessous le code avec la partie où ça bloque en rouge (j'ai fait des tests en découpant mon code donc il est - quasi :oops: - certain que ça bloque à cet endroit).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub WriteDataTOTAL(xlWkb As excel.Workbook, NumWsh As Integer, plageDate As String, colonneDate As String, colonneMachine As String) Dim i, countplageDate countplageDate = xlWkb.Worksheets(NumWsh).Range(plageDate).Rows.Count For i = 1 To countplageDate xlWkb.Worksheets(NumWsh).Range(colonneMachine & i + 1) = _ WorksheetFunction.Index(xlWkb.Worksheets(NumWsh).Range("SOURCES_EFFECTIFS_NB"), _ WorksheetFunction.Match( _ xlWkb.Worksheets(NumWsh).Range(colonneMachine & "1"), _ IIf(xlWkb.Worksheets(NumWsh).Range("SOURCES_EFFECTIFS_DATE") = xlWkb.Worksheets(NumWsh).Range(colonneDate & "2"), _ xlWkb.Worksheets(NumWsh).Range("SOURCES_EFFECTIFS_FILTRE"), ""), 0)) Next i End Sub
L'appel de la procédure:
Call WriteDataTOTAL(xlWkb, 1, "TOTAL_DATE", "K", "L")

Je me dis que ça doit être tout bête: je fais dans mon Iif une comparaison d'un range et d'une valeur, mais ça ne marche pas, j'ai également testé en ajoutant.Value dans le membre à droite du égal.

Merci par avance pour votre aide.

Formule matricielle lancée depuis VBA access vers excel

Bonjour,

J'ai tenté une autre approche que mon code précédent. Dans excel, je valide ma formule par ctrl/maj/entrée puisqu'il s'agit d'une fonction matricielle. Or je ne l'ai pas indiqué dans mon code access. J'ai donc tenté ceci:
Code:

1 2 3 4 xlWkb.Worksheets(NumWsh).Range(colonneMachine & i + 1).Select Selection.FormulaArray = _ "=INDEX(SOURCES_EFFECTIFS_NB,MATCH(""M1"",IF((SOURCES_EFFECTIFS_DATE=""2013_2""),SOURCES_EFFECTIFS_FILTRE,""""),0))"
Ce code fonctionne mais mes critères sont tapés en dur. Si je tente de remettre le paramètre, ça ne fonctionne plus:
Code:

1 2 3 xlWkb.Worksheets(NumWsh).Range(colonneMachine & i + 1).Select Selection.FormulaArray = _ "=INDEX(SOURCES_EFFECTIFS_NB,MATCH(" & colonneMachine & " ""1"",IF((SOURCES_EFFECTIFS_DATE=" & colonneDate & i + 1 & "),SOURCES_EFFECTIFS_FILTRE,""""),0))"
J'ai alors un message d'erreur

Citation:

Impossible de définir la propriété FormulaArray de la classe Range"

Vu que le code fonctionne si les paramètres sont tapés en dur, je me dis que je m'approche de la solution mais comment faire pour faire comprendre que colonneMachine et colonneDate sont des variables définies dans Access?

Merci pour votre aide.

J'avance peu à peu...

J'ai lu sur le net qu'il fallait utiliser la notation L1C1 pour les formules matricielles déclenchées en VBA.

Alors j'ai tenté le code suivant :
Code:

1 2 'appel de procédure: Call WriteDataTOTAL(xlWkb, 4, "TOTAL_DATE", "K", "L1C12")
Code:

1 2 3 4 5 6 7 8 9 'code de la procédure (je laisse tomber la boucle pr le moment) Sub WriteDataTOTAL(xlWkb As excel.Workbook, NumWsh As Integer, plageDate As String, colonneDate As String, colonneMachine As String) Dim i, countplageDate countplageDate = xlWkb.Worksheets(NumWsh).Range(plageDate).Rows.Count xlWkb.Worksheets(NumWsh).Range("L2").Select Selection.FormulaArray = _ "=INDEX(SOURCES_EFFECTIFS_NB,MATCH(" & colonneMachine & ",IF((SOURCES_EFFECTIFS_DATE=""2013_2""),SOURCES_EFFECTIFS_FILTRE,""""),0))" End Sub
ça ne plante pas mais qd je vais voir le fichier excel en L2, j'ai la formule suivante qui est inscrite:

Citation:

=INDEX(SOURCES_EFFECTIFS_NB;EQUIV('L1C12';SI((SOURCES_EFFECTIFS_DATE="2013_2");SOURCES_EFFECTIFS_FILTRE;"");0))

Il y a donc des quotes qui entourent L1C12 donc excel ne le résout pas. Pourquoi le programme ajoute des quotes ?

Merci !

Après pas mal de recherches, j'ai finalement modifié mon code comme ceci:

Code:

1
2
3
4
5
6
7
8
Sub WriteDataTOTAL(xlWkb As excel.Workbook, NumWsh As Integer, plageDate As String, colonneDate As String, colonneMachine As String)
    Dim i, countplageDate
    countplageDate = xlWkb.Worksheets(NumWsh).Range(plageDate).Rows.Count
    For i = 1 To countplageDate
        xlWkb.Worksheets(NumWsh).Range(colonneMachine & i + 1).FormulaArray = _
                "=INDEX(SOURCES_EFFECTIFS_NB,MATCH(" & colonneMachine & "1" & ",IF((SOURCES_EFFECTIFS_DATE=" & colonneDate & i + 1 & "),SOURCES_EFFECTIFS_FILTRE,""""),0))"
    Next i
End Sub

et ça marche :)