[RESOLU] Récupération d'une plage d'un classeur fermé - XL2010

[Ternoise]

Bonjour le forum

J'ai une fonction (connu de tous) qui fonctionne très bien sur XL 2003.

Sur un autre PC avec XL 2010, ça ne marche plus.

Je vous met 2 fichiers de démo en pièces jointes.

Mettre ces 2 fichiers dans un répertoire ""c:\Fichtemp"" et ouvrir ""Classeur_Cible""

Merci de votre aide

David

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Bonjour Ternoise,

VBA paraît inutile, pourquoi ne pas utiliser une formule de liaison ?

Ouvrez les 2 fichiers et en B9 de Classeur_Cible.xlsm entrez la formule :

=INDEX(Classeur_Source.xlsm!T;LIGNES(B$9:B9);COLONNES($B9:B9))

A copier sur tout le tableau.

On peut ensuite fermer Classeur_Source.xlsm.

Pour masquer les valeurs zéro utilisez par exemple le format personnalisé 0;;

A+

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Bonjour et merci Job75

J'avais déjà vu cette solution mais elle ne correspond pas à mon projet.
Le ""Classeur_source"" est "bridé" par un digicode à l'ouverture et il y aurait donc trop de manipulation à faire avant.

Depuis des années, j'utilisé cette fonction et c'était vraiment nickel !

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

L'inconvénient de la méthode ADO c'est que ses paramètres doivent être modifiés quand on change de version Excel.

Je n'ai pas Excel 2010 sous la main.

Quant au "bridage" du fichier source ce n'est vraiment pas un problème !!!

On peut ne pas l'ouvrir et modifier la formule du post #2 en ajoutant le chemin d'accès, par exemple :

=INDEX('C:\Fichtemp\Classeur_Source.xlsm'!T;LIGNES(B$9:B9);COLONNES($B9:B9))

A+

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Mon tableau (T), à ce jour, à plus de 20 000 lignes et j'ajoute 20 lignes par jour.

Le calcul va être long sans méthode ADO, non ?

A copier sur tout le tableau

Je vais peut être attendre une autre solution ! Le problème c'est que je suis coincé depuis 5 jours. Je ne récupère plus ces données importantes. Encore Merci Job75

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Pas certain qu'ADO soit plus rapide que des formules de liaison, c'est à voir.

Testez votre tableau T de 20000 lignes avec cette macro qui entre une formule de liaison matricielle :

Sub Import()
Dim fichier$, f$, nlig&, ncol%
'---préparation---
fichier = ThisWorkbook.Path & "\Classeur_Source.xlsm" 'à adapter
f = "'" & fichier & "'!T"
nlig = ExecuteExcel4Macro("ROWS(" & f & ")")
ncol = ExecuteExcel4Macro("COLUMNS(" & f & ")")
'---restitution du tableau T en B9---
Application.ScreenUpdating = False
Range([B9], Cells(Rows.Count, Columns.Count)).ClearContents 'RAZ
With [B9].Resize(nlig, ncol)
  .FormulaArray = "=" & f 'formule matricielle
  .Value = .Value 'suppression des formules
  .Replace 0, "", xlWhole 'suppression des zéros
End With
End Sub

Bien sûr les 2 fichiers doivent être dans le même répertoire.

A+

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Oups désolé !

Le "Classeur_Cible" n'est pas dans le même répertoire que le "Classeur_Source" !

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Oups désolé !

Le "Classeur_Cible" n'est pas dans le même répertoire que le "Classeur_Source" !

Eh bien ne restez pas les bras ballants, écrivez le chemin en dur à la place de ThisWorkbook.Path !

En testant je m'aperçois que l'instruction :

.Replace 0, "", xlWhole 'suppression des zéros

prend beaucoup de temps, il vaut mieux l'éviter et utiliser :

Sub Import()
Dim t#, fichier$, f$, nlig&, ncol%
t = Timer
'---préparation---
fichier = ThisWorkbook.Path & "\Classeur_Source.xlsm" 'à adapter
f = "'" & fichier & "'!T"
nlig = ExecuteExcel4Macro("ROWS(" & f & ")")
ncol = ExecuteExcel4Macro("COLUMNS(" & f & ")")
'---restitution du tableau en B9---
Application.ScreenUpdating = False
Rows("9:" & Rows.Count).Delete  'RAZ
With [B9].Resize(nlig, ncol)
  .FormulaArray = "=" & f 'formule matricielle
  .Value = .Value 'suppression des formules
  .Name = "T" 'nomme la plage
  .NumberFormat = "0;;" 'masque les zéros
  .Borders.Weight = xlThin 'bordures
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \s") 'mesure facultative
End Sub

Pour tester j'ai redéfini le nom T dans Classeur_Source.xlsm par =DIRECTION!$B$9:$R$20008

La macro s'exécute en 1,52 seconde sur Win 8 - Excel 2013, c'est excellent.

Edit : j'ai revu la RAZ.

Fichiers joints, à télécharger dans le même répertoire (le bureau).

A+

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Bon j'ai adapté votre code avec ADO.

Il fonctionne bien sur Excel 2013, je pense qu'il en va de même sur Excel 2010 et Excel 2007.

Là aussi téléchargez les 2 fichiers joints dans un même répertoire (le bureau).

Chez moi la durée d'exécution est de 1,50 seconde, la différence est donc insignifiante.

Le seul avantage ici est qu'il n'y a pas de zéros dans le tableau...

A+

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Notez que votre code dans la macro Import :

With .Range("B9", .Cells(UBound(Arr, 1) + 8, UBound(Arr, 2) + 1))

n'est guère habile, mieux vaut utiliser Resize :

With .[B9].Resize(UBound(Arr), UBound(Arr, 2)) 'plus habile

Fichier (2).

Bonne fin de soirée.

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re Job75

Merci de vous intéresser à mon problème. C'est super sympa
Donc j'ai essayé les deux fichiers, c'est vraiment impeccable.

Je copie les codes du module dans mon classeur situé "C:\DOCUMENTS VALERIE\CHRONOS - Anticipation d'horaire.xlsm"

Je change bien sûre

Fich = ThisWorkbook.Path & "\Classeur_Source.xlsm" 'à adapter

avec le vrai endroit du fichier source

Fich = "C:\DOCUMENTS SALARIES\Chronos.xlsm" 'à adapter

et j'ai un message d'erreur (photo) à cette ligne

Arr(RS_n, RS_f + 1) = myRS.Fields(RS_f).Value

Je pense avoir fait les choses correctement ! ???
David

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Aucune idée, ni du pourquoi, ni du comment.

Bonne nuit.

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re,

Ha bin la, je suis dans le cambouis !

Je fais des essais avec même mise en page, même nom de feuille, moins de données, etc...

Avec vos fichiers, nickel...Avec un copier/Coller du module dans mon classeur, erreur

Peut être après une bonne nuit !

Encore merci Job75

 

[job75]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Bonjour Ternoise, le forum,

Ha bin la, je suis dans le cambouis !

Et que donne la solution par formule de liaison du post #8 ?

Bonne journée.

 

[Ternoise]

Re : Récupération d'une plage d'un classeur fermé - XL2010

Re Job75

Une bonne nuit de sommeil et rien n'y fait.

J'ai du mal à adapter avec la formule de liaison. Je garde sous le coude si avec ADO il n'y a pas de solution !

Concernant avec ADO

J'ai épuré complètement les deux fichiers et j'ai toujours le même problème

En pièces jointes, mes 2 vrais fichiers (""CHRONOS - Anticipation d'horaire.xlxm"" est le fichier problématique)

Vraiment merci de votre aide

David