XL 2013 récup dans fichier fermés astuce names(aternative a ADO)

patricktoulon · 5 Octobre 2020

Bonjour a tous
je fait un peu le menage dans mes fonctions et j'ai retrouvé un truc que j'aimerais bien résoudre
en effet en alternative a ADO il m'arrive d'utiliser une astuce assez simple qui consiste à utiliser un name dans le quel j'ai un lien d'une plage dans un fichier fermé et l'utiliser sur une plage de meme dimension

sauf que si la plage destination et la plage source n'ont pas la meme address j'ai des "#Valeur"dans les cellules qui sont décalées dans la destination

en gros si ma plage source commence en "B" et que je la pose en A dans destination la colonne A aura des "#Valeur"
et pareil pour les lignes

VB:

Option Explicit
Sub test1()
'pour une plage classique
    Dim chemin$, fichier$, feuille$, rngsource As Range, rngdestination As Range
    chemin = ThisWorkbook.Path & "\"    'ne pas oublier le dernier slach
    fichier = "source.xlsx"
    feuille = "toto"
    Set rngsource = [b4:d10]
    Set rngdestination = Sheets("Feuil1").[A1]
    GetTableOnClosedFich chemin, fichier, feuille, rngsource, rngdestination

End Sub



Sub GetTableOnClosedFich(chemin$, fichier$, feuille$, rng As Range, rngD As Range)
    ThisWorkbook.Names.Add "plage", RefersTo:="='" & chemin & "[" & fichier & "]Feuil1'!" & "Tableau1"
    
    ' me donne qu'une partie du tableau
    With rngD.Resize(rng.Rows.Count, rng.Columns.Count)
        .Value = "=plage"
        .Value = .Value
    End With

    ' me donne le tableau mais en partant de A1 de la source et donc des ligne et ou colonnes "#VALEUR"
    With rngD.Resize(rng.Rows.Count + 3, rng.Columns.Count + 1)
        .Value = "=plage"
        .Value = .Value
    End With
    
    ThisWorkbook.Names("plage").Delete
End Sub

c'est domage pour des petite plage c'est une alternative intéressante a ADO qui est verbeux
allez les cracks

Hasco · 5 Octobre 2020

Bonjour,

Avec la fonction ci-dessous, cela fonctionne correctement sur une plage de cellule, je n'ai pas testé pour un Tableau structuré

VB:

Sub GetTableOnClosedFich(chemin$, fichier$, feuille$, rng As Range, rngD As Range)
    Dim Formule As String
    Formule = "='" & chemin & "[" & fichier & "]Feuil1'!" & rng.Address
    
    Debug.Print Formule ' pour contrôle
    
    With rngD.Resize(rng.Rows.Count, rng.Columns.Count)
        .FormulaArray = Formule
        .Value = .Value
    End With

End Sub

Cordialement

Lolote83 · 5 Octobre 2020

Bonjour PatrickToulon,
Je ne sais pas si cela peut t'aider, mais ta plage [B410] du fichier source aura comme données (vides) si celui-ci n'est pas actif
Ici, le fichier source est fermé et voici ce que j'ai

J'ai le même phénomène si le fichier source est ouvert mais non actif. Je continue à chercher au cas ou
@+ Lolote83

patricktoulon · 5 Octobre 2020

bonjour @Lolote83
merci pour le coup de main

Usine à gaz · 5 Octobre 2020

Bonjour Lolote83, Bonjour Patrick, Le Forum,

En espérant ne pas être hors sujet, voici un code qui a été fait pars Job75 suite à ma demande.
Voir pièces jointes (à mettre dans le même dossier).
Amicalement,
lionel,

Hasco · 5 Octobre 2020

Bonjour,

Avec la fonction ci-dessous, cela fonctionne correctement sur une plage de cellule, je n'ai pas testé pour un Tableau structuré

VB:

Sub GetTableOnClosedFich(chemin$, fichier$, feuille$, rng As Range, rngD As Range)
    Dim Formule As String
    Formule = "='" & chemin & "[" & fichier & "]Feuil1'!" & rng.Address
    
    Debug.Print Formule ' pour contrôle
    
    With rngD.Resize(rng.Rows.Count, rng.Columns.Count)
        .FormulaArray = Formule
        .Value = .Value
    End With

End Sub

Cordialement

patricktoulon · 5 Octobre 2020

re
hoh!!!!!! puré
et bien sur c'est une plage de cellules la destination donc formulaarray
et je suis un benet

merci @Reblochon je l'avais devant les yeux je voyais rien
et en prime on fait sauter le name de l’équation

je ressort mes vielles macros

et
ouaip!!! faudrait essayer maintenant avec les tableaux
ça va être coton pour l'addresse non?

Hasco · 5 Octobre 2020

Re,

Pour les tableaux, en tout cas sur votre 'source.xlsx' cela ne fonctionne pas, corromps le fichier source qui est réparé à l'ouverture suivante (Tableau1 devient Tableau1_1) et je ne peux plus utiliser le nom Tableau1.

Cela ne m'étonne qu'à moitié, les tableaux structurés faisant appel à d'autres technologies que les tableaux excel natifs.

Cordialement

patricktoulon · 5 Octobre 2020

re
tu a essayé quoi comme string formule pour les tableaux

Hasco · 5 Octobre 2020

re,

La même en remplaçant l'adresse finale par Tableau1 et même Tableau1[#All] pour récupérer l'entête.

Cela semble fonctionner manuellement, mais si on ferme source et récap et qu'on rouvre récap on obtient un tableau de #/REF !!!

Les tableaux sont gérés différemment à l'évidence puisqu'on ne peut pas changer leur adresse dans le gestionnaire de noms et qu'il faut passer par le bouton 'Redimensionner le tableau'

bonne recherche

mapomme · 5 Octobre 2020

Bonsoir à tous,

Ah Les bonnes méthodes d'antan !
On voit bien que le tableau structuré est une verrue (selon moi).

patricktoulon · 5 Octobre 2020

et oui ouvert on peux le choper ou par etape chopper la cel(1) et la dernière du tableau mais fermé bye!bye!

patricktoulon · 5 Octobre 2020

Bonjour @mapomme

mapomme à dit:
Bonsoir à tous,

Ah Les bonnes méthodes d'antan !
On voit bien que le tableau structuré est une verrue (selon moi).

ben oui je rangeais mes not' dans le dossier macro sur fichiers fermés et j'ai retrouvé ces petits trucs
je l'ai en executeexcel4macro aussi dans une variable tableau

faut pas forcement tout jeter

Hasco · 5 Octobre 2020

Re,
Hello @mapomme

vieille ou ancienne méthode je prends tout, l'essentiel étant que je m'en serve à bon escient.

@patricktoulon :

Il y a fort longtemps maintenant avec ce genre de procédure, j'avais rencontré un problème de temps de recalcul le .FormulaArray = Formule et la ligne .Value = .Value.

Pour le résoudre j'avais fais appel à Application.CalculationState pour attendre qu'il passe à xlDone avant la ligne .value = .value

Mais c'est vrai, que nos ordi sont tellement plus rapides aujourd'hui.

Cordialement

mapomme · 5 Octobre 2020

Salut @Roblochon

Roblochon à dit:
vieille ou ancienne méthode je prends tout, l'essentiel étant que je m'en serve à bon escient.

Je n'ai jamais dit le contraire.

patricktoulon · 5 Octobre 2020

re
tien la version executeexcel4macro
je l'ai réadapté au fichier exemple enpost1

VB:

Sub test2()
'pour une plage classique
    Dim chemin$, fichier$, feuille$, rngsource As Range, rngdestination As Range
    chemin = ThisWorkbook.Path & "\"    'ne pas oublier le dernier slach
    fichier = "source.xlsx"
    feuille = "toto"
    Set rngsource = [b4:d10]
    Set rngdestination = Sheets("Feuil1").[A1]
    GetTableOnClosedFich2 chemin, fichier, feuille, rngsource, rngdestination

End Sub



Sub GetTableOnClosedFich2(chemin$, fichier$, feuille$, rng As Object, rngD As Range)
    Dim tabl, lig&, col&
    ReDim tabl(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For lig = 1 To UBound(tabl)
        For col = 1 To UBound(tabl, 2)
            tabl(lig, col) = ExecuteExcel4Macro("'" & chemin & "[" & fichier & "]toto'!" & rng.Cells(lig, col).Address(, , xlR1C1))
        Next
    Next
    Application.ScreenUpdating = False
    rngD.Resize(rng.Rows.Count, rng.Columns.Count).Value = tabl
    Application.ScreenUpdating = True
End Sub

il faudra que je teste sur une grande plage pour tester la plus rapide

XL 2013 récup dans fichier fermés astuce names(aternative a ADO)

patricktoulon

XLDnaute Barbatruc

Pièces jointes

Hasco

Lolote83

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

Hasco

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Discussions similaires