XL 2016 vba : Faire une recherche de données dans fichier externe fermé

[san san]

bonjour à tous,

je m'en remets à vous pour résoudre un problème :

dans le cadre de mon travail, je me suis créée un fichier excel avec un onglet pour chaque semaine dans lesquels il y a une table (identique) avec différentes données.

Ensuite sur un nouvel onglet je me suis créée une macro afin d'effectuer une rechercher d'une référence saisie, et qui vient me coller toutes les lignes trouvées dans chaque onglet, de cette ref.

jusque là tout va bien sauf que mon fichier atteint le 200mo et la macro devient lente.

Je me suis dis : je n'ai qu'à mettre mes données dans un fichier, et mon fichier+macro dans un autre

c'est là que ca coince, je ne sais pas aller chercher mes données dans un fichier externe

Déjà, est ce que mon idée va améliorer quelque chose, sinon pas la peine de se prendre la tete ?

merci d avance à quiconque pourra m'éclairer

PS : je débute en vba depuis quelque mois à peine alors soyez indulgents avec moi et bonne année

Sub FIND()
'


Dim ref As Variant
Dim i As Long, j As Long                            'Déclare deux variables numériques
Dim plage As Range



ref = Sheets("recherche").Range("A1").Value         'reference a chercher en A1


Worksheets("recherche").Range("A3:ae1000").ClearContents   'efface anciens resultats de la recherche

Application.ScreenUpdating = False

' Passe chaque feuille en revue en partant de la 3eme

For i = 3 To Worksheets.Count                   'WorkSheets.Count donne le nombre total de feuilles

Worksheets(i).Activate

If Worksheets(i).FilterMode Then Worksheets(i).ShowAllData     'retire filtres si il y a


    Set plage = ActiveSheet.Range("A2").CurrentRegion            'selectionne plage tableau
   
    plage.AutoFilter Field:=7, Criteria1:=ref                    ' filtre ref sur colonne G
   
    On Error Resume Next
   
    Worksheets(i).Range("A2:AZ" & Range("A100000").End(xlDown).Row).SpecialCells(xlVisible).Copy ' copie le filtre

    Worksheets("recherche").Activate
 
    If Range("A3") = "" Then
   
            Range("A3").PasteSpecial Paste:=xlPasteValues                   ' colle
         
           
            Else
           
   
    Worksheets("recherche").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues     ' colle la selection
   
    End If

   
   
   

Next 'Passe à la feuille suivante

Application.ScreenUpdating = True


End Sub

apercu du resulat :

 

[shinozak]

Bonjour bonnes fêtes,

Ce n'est pas la macro qui prend de la place, la dissocier ne servirai à rien par contre tu pourrais mettre ton classeur au format binaire xslb au lieu de xlsm, et rajouter ces instructions :

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False

...

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  ActiveSheet.DisplayPageBreaks = True
  Application.ScreenUpdating = True

 

[san san]

Bonjour bonnes fêtes,

Ce n'est pas la macro qui prend de la place, la dissocier ne servirai à rien par contre tu pourrais mettre ton classeur au format binaire xslb au lieu de xlsm, et rajouter ces instructions :

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False

...

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  ActiveSheet.DisplayPageBreaks = True
  Application.ScreenUpdating = True

bonjour Shinozak

alors je viens de tester en xlsb, effectivement mon fichier a fait un regime de 140Mo en moins lol

parcontre la recherche est toujours longue environ 15/20sec (ou alors c'est moi qui suis trop impatiente )

 

[shinozak]

Ça me semble correct, 15-20 pour une recherche+copie dans un classeur de +50mo. Un peu de patience

 

[san san]

ok alors je patienterai

 

[James007]

Bonjour Sandrine,

Pour lire un classeur fermé sans l'ouvrir ...et le faire de façon instantanée ...

une solution s'impose ADO

Dans le fil suivant, le fichier Démo que Pierre a posté va grandement t'aider ...

Lire dans classeur fermé

Bonjour à tous, je débarque dans le forum et comme vous pouvez lire dans mon nom... je vais pas vite!!! Ca fait 2 jours que je surf (ou plutôt patauge ce serait plus conforme à la réalité) sur le net pour résoudre mon problème... Je rève d'une fonction "générique" (ou macro) qui pourrait lire...

www.excel-downloads.com

Bonne Continuation

 

[job75]

Bonjour à tous,

Juste une remarque sur la macro du post #1, remplacer :

Worksheets(i).Range("A2:AZ" & Range("A100000").End(xlDown).Row).SpecialCells(xlVisible).Copy ' copie le filtre

par :

Worksheets(i).Range("A2:AZ" & Range("A100000").End(xlUp).Row).SpecialCells(xlVisible).Copy ' copie le filtre

ce sera plus logique et plus rapide.

Testez en affichant l'adresse de la plage...

A+

 

[san san]

bonsoir James, Job75

j'en avais oublié qu'à la base de mon arrivée sur ce forum j'avais posté un problème lol

> James, j'ai apercu ce terme "ADO" dans mes recherches, mais comme Shinozak, m'a répondu que c'est pas ca qui allait changer quelque chose, j'ai lâché l'affaire, mais je regarderai , ca pourra toujours m'être utile pour autre chose

> Job75, alors j'ai testé, effectivement, ca accélère beaucoup la macro MAIS j'ai plus du tout le résultat escompté ; ce qui est étrange est que sur plusieurs onglets, ca me copie uniquement l'entête (que je ne souhaite pas) et sur certains autres ca fonctionne bien??

 

[James007]

Bonsoir Sandrine,

Ce sujet n'est à creuser ... QUE SI tu dois absolument extraire des données de fichiers Excel fermés et que tu ne veux pas les ouvrir ... soit parce qu'ils sont trop volumineux ou alors qu'il y en a plusieurs à manipuler ...

 

[san san]

coucou,

justement mon fichier est très gros, puisque j'ai un onglet pour chaque semaine qui avoisine les 100000 lignes chacun. Donc mon idée faussement géniale était de faire ma "base de données" dans un fichier1 et de mettre ma macro dans un autre fichier2 "vide" qui viendrait chercher les données dans le fichier1.

 

[James007]

Re,

Je ne pouvais pas le deviner ... mais, dans les circonstances que tu décris ... tu peux oublier " faussement " ...!!!

Il est certain que ton idée est PARFAITE ...!!!

 

[san san]

Job75 : finalement, c'est bon, en cherchant un peu, j'avais une ligne vide dans une de mes tables(surement une extraction foireuse) je l'ai supprimée, et tout va pour le mieux et beauuuuuuucoup plus rapidement. Merci beaucoup.

James : alors je vais tenter d'explorer cette piste, meme si la conversion du xldown en xlUp a résolu mon soucis de lenteur, mais juste pour le "fun" et me fixer un objectif pour apprendre autre chose encore.... et si je galère je sais où trouver de l'aide

 

[James007]

James : alors je vais tenter d'explorer cette piste, même si la conversion du XLDown en XLUp a résolu mon souci de lenteur, mais juste pour le "fun" et me fixer un objectif pour apprendre autre chose encore.... et si je galère je sais où trouver de l'aide

No problemo