Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

p'tit vieux

XLDnaute Occasionnel
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
 

Pièces jointes

  • Fonction.xlsm
    21.4 KB · Affichages: 8
  • Datas externes.xlsx
    17.3 KB · Affichages: 10

job75

XLDnaute Barbatruc
Bonjour p'tit vieux, le forum,

Après avoir créé la fonction Total on peut créer la fonction MaMoyenne :
VB:
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+
 

Pièces jointes

  • Moyenne Liaisons.xlsm
    27.3 KB · Affichages: 3
  • Datas externes.xlsx
    10.2 KB · Affichages: 0

patricktoulon

XLDnaute Barbatruc
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

VB:
'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

XLDnaute Occasionnel
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é
 

patricktoulon

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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 :
VB:
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
 

Pièces jointes

  • Total Liaisons.xlsm
    26.9 KB · Affichages: 0
  • Datas externes.xlsx
    10.2 KB · Affichages: 0

patricktoulon

XLDnaute Barbatruc
re
si avoir les formules dans la variable tableau est compliqué pour toi
utilise la même mais avec les macro4
VB:
'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

XLDnaute Barbatruc
On pourra créer de la même manière les fonctions MonMin et MonMax :
VB:
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
 

Pièces jointes

  • Min Max Liaisons.xlsm
    28.5 KB · Affichages: 1
  • Datas externes.xlsx
    10.2 KB · Affichages: 0
Dernière édition:

p'tit vieux

XLDnaute Occasionnel
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 !!
 

Pièces jointes

  • Premier pas1.gif
    787.4 KB · Affichages: 11

p'tit vieux

XLDnaute Occasionnel
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

XLDnaute Occasionnel

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

XLDnaute Barbatruc
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
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…