XL 2021 Problème d'accès aux datas d'une autre feuille fermée

[p'tit vieux]

Bonjour à tous,
Comme le titre l'indique j'ai un petit souci dans un UDF que j'espère pouvoir vous soumettre bientôt..
Je sais que c'est un sujet assez récurant mais je n'ai rien lu qui résolve mon exemple.
Voici le problème ...
J'ai une fonction dans un fichier (Fonction.xlsm) qui lit des valeurs dans un autre fichier (Datas externes.xlsx).

1. Lorsque celui-ci est ouvert pas de souci.
2. Lorsqu'il est fermé j'ai 2 cas:
   1. Si c'est une plage de cellule => >Je reçois un tableau (ca me gène mais ca se gère)
   2. Si plusieurs Areas (zones) là j'ai une erreur 2015.

Je sais que la principale solution est d'ouvrir (masqué !) le fichier par code VBA j'ouvre des données externes mais, dans ma macro, je n'ai pas réussi à ouvrir le fichier(Datas externes.xlsx) avec ce qui est proposé dans les forums. Pour l'instant, je ne comprend pas pourquoi mais c'est ainsi.
Par contre je vois un défaut au fait d'ouvrir un fichier: Par exemple, imaginé le temps d'ouverture avec un gros fichier complexe avec des calculs dans tous les sens

Donc si quelqu'un à une ou plusieurs idées
Cordialement à tous

 

[job75]

Bonjour p'tit vieux, le forum,

Après avoir créé la fonction Total on peut créer la fonction MaMoyenne :

Function MaMoyenne(ParamArray tablo()) As Variant
Dim e As Variant, ee As Variant, n&, s As Double
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then
                n = n + 1
                s = s + CDbl(ee)
            End If
        Next ee
    Else
        If IsNumeric(CStr(e)) Then
            n = n + 1
            s = s + CDbl(e)
        End If
    End If
Next e
If n Then MaMoyenne = s / n Else MaMoyenne = ""
End Function

A+

 

[patricktoulon]

bonjour @p'tit vieux
je t'ai dis en post#2
je cite

voir même encore plus simple les formule de liaison

sincèrement même si j'adore jouer avec ADO
je pense que pour le travail de récupération que tu veux faire
a savoirrécupérer des lignes non contigues d'une colonne)
cette méthode suffit
à savoir je répète; injecter des formules de liaison dynamiquement par vba
puis si c'est necessaire convertir la formule en .value

'patricktoulon
'exemple; '='chemin & "\[" & nomfichier &"]" & nomdelafeuille & "'!" address de cellule"
Sub test_récup_plage2()
    Dim Chemin$, fichier$, Feuille$, T, plage As Range
    Chemin$ = ThisWorkbook.Path    'à adapter
    fichier$ = "Datas externes.xlsx"   'à adapter
    Set plage = [C11:C15,C20:C21,C18]    'la plage en range
    Feuille$ = "Feuil1"    'le non de la feuille (Attention pas le codename mais bien le nom que vous lui avez donné)
    T = GetRowNOnColumnCloseFich(Chemin, fichier, "Feuil1", plage)

    With [A1].Resize(UBound(T))
        .Value = T
        .Value = .Value
    End With
End Sub
Function GetRowNOnColumnCloseFich(Chemin As String, fich As String, Feuille As String, rng As Range)
    Dim tbl, I&
    ReDim tbl(1 To rng.Cells.Count)
    For Each cel In rng.Cells
        I = I + 1
        tbl(I) = "='" & Chemin & "\[" & fich & "]" & Feuille & "'!" & cel.Address
    Next
    GetRowNOnColumnCloseFich = Application.Transpose(tbl)
End Function

rien ne t'empêche de le dimentionner avec une ligne de plus et dans cette ligne lui mettre la somme avec worksheetfunction.sum(tbl)

 

[p'tit vieux]

Bonjour @patricktoulon , bonjour à tous
Je vois. Tu affectes toutes les adresses des cellules demandées aux cellules de destination puis tu recopies par dessus les valeurs dans celles-ci.
Très bonne idée … z'y avais pas pensé.
Ca marche bien est c'est plus simple que de manipuler l'ADO.
Maintenant il faut que je vois si je peux gérer/incorporer cela dans mon UDF.
J'espère pouvoir le mettre en ligne d'ici peu.
C'est un UDF très particulier. Vous verrez, j'ai inventé la roue … carré

 

[dysorthographie]

Dupuis bien longtemps L'UDF est devenu le MODEM

 

[patricktoulon]

Bonjour @patricktoulon , bonjour à tous
Je vois. Tu affectes toutes les adresses des cellules demandées aux cellules de destination puis tu recopies par dessus les valeurs dans celles-ci.
Très bonne idée … z'y avais pas pensé.
Ca marche bien est c'est plus simple que de manipuler l'ADO.
Maintenant il faut que je vois si je peux gérer/incorporer cela dans mon UDF.
J'espère pouvoir le mettre en ligne d'ici peu.
C'est un UDF très particulier. Vous verrez, j'ai inventé la roue … carré

dans ton udf tu incorporait la varaible tableau retourné par ado et le getrows
ben la tu renvoie la fonction tout court
dans la variable tableau il y a tout les formules de liaison

une roue carrée ca va pas tourner bien rond tout ça

 

[job75]

Du coup je viens de me rendre compte que pour calculer Total il ne faut pas utiliser Application.Sum.

En effet la revalidation de E4 crée une valeur d'erreur, il faut donc utiliser :

Function Total(ParamArray tablo()) As Double
Dim e As Variant, ee As Variant
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then Total = Total + CDbl(ee)
        Next ee
    Else
        If IsNumeric(CStr(e)) Then Total = Total + CDbl(e)
    End If
Next e
End Function

 

[p'tit vieux]

Dupuis bien longtemps L'UDF est devenu le MODEM

 

[patricktoulon]

re
si avoir les formules dans la variable tableau est compliqué pour toi
utilise la même mais avec les macro4

'patricktoulon
'exemple; '='chemin & "\[" & nomfichier &"]" & nomdelafeuille & "'!" address de cellule"
Sub test_récup_plagemacro4()
    Dim Chemin$, fichier$, Feuille$, T, plage As Range
    Chemin$ = ThisWorkbook.Path    'à adapter
    fichier$ = "Datas externes.xlsx"   'à adapter
    Set plage = [C11:C15,C20:C21,C18]    'la plage en range
    Feuille$ = "Feuil1"    'le non de la feuille (Attention pas le codename mais bien le nom que vous lui avez donné)
    T = GetRowNOnColumnCloseFich(Chemin, fichier, "Feuil1", plage)

    [A1].Resize(UBound(T)).Value = T
      
End Sub
Function GetRowNOnColumnCloseFich(Chemin As String, fich As String, Feuille As String, rng As Range)
    Dim tbl, I&
    ReDim tbl(1 To rng.Cells.Count)
    For Each cel In rng.Cells
        I = I + 1
         tbl(I) = ExecuteExcel4Macro("'" & Chemin & "\[" & fich & "]" & Feuille & "'!" & cel.Address(, , xlR1C1))
     Next
    GetRowNOnColumnCloseFich = Application.Transpose(tbl)
End Function

voila c'est simple

 

[job75]

On pourra créer de la même manière les fonctions MonMin et MonMax :

Function MonMin(ParamArray tablo()) As Double
Dim e As Variant, ee As Variant
MonMin = Val("1.79769313486231570E+308")
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then If CDbl(ee) < MonMin Then MonMin = CDbl(ee)
        Next ee
    Else
        If IsNumeric(CStr(e)) Then If CDbl(e) < MonMin Then MonMin = CDbl(e)
    End If
Next e
End Function

Function MonMax(ParamArray tablo()) As Double
Dim e As Variant, ee As Variant
MonMax = Val("-1.79769313486231570E+308")
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then If CDbl(ee) > MonMax Then MonMax = CDbl(ee)
        Next ee
    Else
        If IsNumeric(CStr(e)) Then If CDbl(e) > MonMax Then MonMax = CDbl(e)
    End If
Next e
End Function

Si tout ça n'intéresse pas p'tit vieux tant pis pour lui

 

[p'tit vieux]

Pour faire durée le suspense de la roue carrée voici un aperçu de celle-ci

Il s'agit d'une sorte de "RechercheX". Recherches et résultats multiples.
Mais chuuut !!

 

[p'tit vieux]

On pourra créer de la même manière les fonctions MonMin et MonMax :

Function MonMin(ParamArray tablo()) As Double
Dim e As Variant, ee As Variant
MonMin = Val("1.79769313486231570E+308")
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then If CDbl(ee) > MonMin Then MonMin = CDbl(ee)
        Next ee
    Else
        If IsNumeric(CStr(e)) Then If CDbl(e) < MonMin Then MonMin = CDbl(e)
    End If
Next e
End Function

Function MonMax(ParamArray tablo()) As Double
Dim e As Variant, ee As Variant
MonMax = Val("-1.79769313486231570E+308")
For Each e In tablo
    If IsArray(e) Then
        For Each ee In e
            If IsNumeric(CStr(ee)) Then If CDbl(ee) > MonMax Then MonMax = CDbl(ee)
        Next ee
    Else
        If IsNumeric(CStr(e)) Then If CDbl(e) > MonMax Then MonMax = CDbl(e)
    End If
Next e
End Function

Si tout ça n'intéresse pas p'tit vieux tant pis pour lui

Bonjour Job75.
Oui ca marche mais non ca ne marche pas dans mon cas.
En fait j'ai une fonction recevant plusieurs paramètres (voir ci-dessus captures et gif)
Tous sont aux mêmes "formats", pour faire simple ce sont des Ranges.
Function FINDXY(FindWhat As Variant, _
FindWhere As Variant, _
Optional ByVal WhereToRead As Variant, _ etc

Lorsque le fichier "Data Externes" est fermé, je reçois un tableau des valeurs mais pour le 2éme, 3éme j'ai une erreur 2015 (#Value)
Voilà tu sais presque tout.

 

[p'tit vieux]

re
si avoir les formules dans la variable tableau est compliqué pour toi
utilise la même mais avec les macro4

'patricktoulon
'exemple; '='chemin & "\[" & nomfichier &"]" & nomdelafeuille & "'!" address de cellule"
Sub test_récup_plagemacro4()
    Dim Chemin$, fichier$, Feuille$, T, plage As Range
    Chemin$ = ThisWorkbook.Path    'à adapter
    fichier$ = "Datas externes.xlsx"   'à adapter
    Set plage = [C11:C15,C20:C21,C18]    'la plage en range
    Feuille$ = "Feuil1"    'le non de la feuille (Attention pas le codename mais bien le nom que vous lui avez donné)
    T = GetRowNOnColumnCloseFich(Chemin, fichier, "Feuil1", plage)

    [A1].Resize(UBound(T)).Value = T
    
End Sub
Function GetRowNOnColumnCloseFich(Chemin As String, fich As String, Feuille As String, rng As Range)
    Dim tbl, I&
    ReDim tbl(1 To rng.Cells.Count)
    For Each cel In rng.Cells
        I = I + 1
         tbl(I) = ExecuteExcel4Macro("'" & Chemin & "\[" & fich & "]" & Feuille & "'!" & cel.Address(, , xlR1C1))
     Next
    GetRowNOnColumnCloseFich = Application.Transpose(tbl)
End Function

voila c'est simple

Ca marche (comme le premier)
C'est sûr que c'est plus simple que ADO.
Mais peux-tu me dire pourquoi cette fois tu utilises "ExecuteExcel4Macro()" ? L'intérêt?
Je me retrousse les manches et je tente son intégration (dans le moyeu de la roue) pour voir ce que cela donne y compris en performance (pour l'instant, fichiers ouverts, je réalise 500 000 recherches en 4s. à 5s -avec 3 résultats retournés par recherche trouvée-)
Merci à toi, merci tous
Je vous tiens au courant.

 

[patricktoulon]

re
l'intérêt comme je l'ai dit, c'est que dans la variable il n'y a plus la formule mais le resultat de la formule

tu veux faire une moyenne un max un min ou je ne sais quoi d'autre comme calcul tu peux le faire avec la variable tableau
avant même de tranférer dans la feuille de destination

 

[p'tit vieux]

re
l'intérêt comme je l'ai dit, c'est que dans la variable il n'y a plus la formule mais le resultat de la formule

tu veux faire une moyenne un max un min ou je ne sais quoi d'autre comme calcul tu peux le faire avec la variable tableau
avant même de transférer dans la feuille de destination

Merci pour l'info

 

[job75]

tu veux faire une moyenne un max un min ou je ne sais quoi d'autre comme calcul tu peux le faire avec la variable tableau

Attention, une formule de liaison et donc ExecuteExcek4Macro donne la valeur zéro aux cellules vides.

Ce n'est pas gênant pour une somme mais ça l'est pour une moyenne, un min ou un max.