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

p'tit vieux · 16 Décembre 2023

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).

Lorsque celui-ci est ouvert pas de souci.
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 · 19 Décembre 2023

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+

patricktoulon · 19 Décembre 2023

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 · 19 Décembre 2023

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 · 19 Décembre 2023

Dupuis bien longtemps L'UDF est devenu le MODEM

patricktoulon · 19 Décembre 2023

p'tit vieux à dit:
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 · 19 Décembre 2023

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

p'tit vieux · 19 Décembre 2023

dysorthographie à dit:
Dupuis bien longtemps L'UDF est devenu le MODEM

patricktoulon · 19 Décembre 2023

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 · 19 Décembre 2023

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

p'tit vieux · 19 Décembre 2023

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 · 19 Décembre 2023

job75 à dit:

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

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 · 19 Décembre 2023

patricktoulon à dit:

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

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 · 19 Décembre 2023

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 · 19 Décembre 2023

patricktoulon à dit:
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 · 19 Décembre 2023

patricktoulon à dit:
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.

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

p'tit vieux

XLDnaute Occasionnel

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

p'tit vieux

XLDnaute Occasionnel

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

p'tit vieux

XLDnaute Occasionnel

Pièces jointes

p'tit vieux

XLDnaute Occasionnel

p'tit vieux

XLDnaute Occasionnel

patricktoulon

XLDnaute Barbatruc

p'tit vieux

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Discussions similaires