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

[patricktoulon]

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

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

 

[Lolote83]

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]

bonjour @Lolote83
merci pour le coup de main

 

[Usine à gaz]

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]

Bonjour,

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

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]

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]

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]

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

 

[Hasco]

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]

Bonsoir à tous,

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

 

[patricktoulon]

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]

Bonjour @mapomme

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]

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]

Salut @Roblochon

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]

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

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