Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

[Schlew]

Bonjour à tous,

J'ai un gros problème de lenteur de macro. Si vous pouviez m'aider ce serait pas mal ;-)
Explication:
Dans une première feuille j'ai, par exemple, 10 lignes.
La macro va supprimer les lignes existantes (sauf la première) dans une deuxième feuille puis copier - insérer la ligne copiée aprés la première ligne 9 fois (car 10 lignes dans la première feuille) avec cette macro:

Dim nb, i, ancienb As Integer
Dim ligne As String
nb = Cells(16, 4).Value
ancienb = Cells(17, 4).Value
For i = 1 To (ancienb - 2)
Rows("33:33").Select
Selection.Delete Shift:=xlUp
Next
For i = 1 To (nb - 2)
Rows("33:33").Select
Selection.Insert Shift:=xlDown
Next
For i = 33 To (33 + nb - 2)
ligne = i & ":" & i
Rows("32:32").Select
Selection.Copy
Rows(ligne).Select
ActiveSheet.Paste
Next

Le problème est que, pour chaque ligne, les calculs des différentes colonnes font références à d'autres fichiers excels (une dizaine à peu près).
4 fichiers différents au total, en utilisant notamment la fonction INDEX. Exemple
INDEX(_NOM1;EQUIV(BF32;_NOM2;0))
_NOM1 et _NOM2 font référence à des colonnes des autres fichiers excels.
En gros la fonction dit: trouve la valeur BF32 dans la colonne _NOM2 du fichier B et donne la valeur correspondante de la colonne _NOM1 dans le fichier A (fichier ou je fais tourner ma macro).

Bref, en gros mon soucis est:
Avec les 4 fichiers fermés, la macro prend une trentaine de secondes.

Avec les 4 fichiers ouverts: 2 secondes.

Avec la macro qui ouvre les fichiers en masqué: une trentaine de seconde:

Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Dim x2App As New Excel.Application
Dim x2Book As New Excel.Workbook
Dim x2Sheet As New Excel.Worksheet
Dim x3App As New Excel.Application
Dim x3Book As New Excel.Workbook
Dim x3Sheet As New Excel.Worksheet
Dim x4App As New Excel.Application
Dim x4Book As New Excel.Workbook
Dim x4Sheet As New Excel.Worksheet

Set xlBook = xlApp.Workbooks.Open("Q:\FICHIERB.xls")
Set x2Book = x2App.Workbooks.Open("Q:\FICHIERC.xls")
Set x3Book = x3App.Workbooks.Open("Q:\FICHIERD.xls")
Set x4Book = x4App.Workbooks.Open("Q:\FICHIERE.xls")

Application.Run "MaMacro"

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
x2Book.Close
x2App.Quit
Set x2Sheet = Nothing
Set x2Book = Nothing
Set x2App = Nothing
x3Book.Close
x3App.Quit
Set x3Sheet = Nothing
Set x3Book = Nothing
Set x3App = Nothing
x4Book.Close
x4App.Quit
Set x4Sheet = Nothing
Set x4Book = Nothing
Set x4App = Nothing

Avec la macro qui permet d'ouvrir les fichiers et de les afficher: un peu mieux: 20 secondes

Workbooks.Open Filename:="Q:\FICHIERB.xls"
Workbooks.Open Filename:="Q:\FICHIERC.xls"
Workbooks.Open Filename:="Q:\FICHIERD.xls""
Workbooks.Open Filename:="Q:\FICHIERE.xls"

Application.Run "MaMacro"

Workbooks("FICHIERB.xls").Close SaveChanges:=False
Workbooks("FICHIERC.xls").Close SaveChanges:=False
Workbooks("FICHIERD.xls").Close SaveChanges:=False
Workbooks("FICHIERE.xls").Close SaveChanges:=False

La dernière solution n'est pas top car d'autres utilisateurs (ou même moi même) peuvent avoir les fichiers B,C,D ou E ouverts (ce qui implique des fenètres du style: le fichier est déjà ouvert......).

Bref, j'ai plus trop de solution qui me permet d'appliquer ma macro en 2 secondes comme avec les fichiers ouverts.

Si vos lanternes pouvaient m'éclairer.......

PS: Je n'y comprends pas grand chose en macro ni en excel d'ailleurs (je me suis très largement inspiré du travail des autres (oui je sais, c'est vilain) pour faire mon fichier et mes macros (merci à ceux qui s'y connaissent d'ailleurs, ceux de ce forum ou autres) donc soyez indulgent avec vos réponses.
PS2: Les fichiers sont sur un serveur donc à chaque ouverture il doit les télécharger (si ça peut aider)

Merci.

Schlew

 

[Dranreb]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Bonsoir.
Mettez déjà au début de la macro:

Application.Calculation = xlCalculationManual

Et à la Fin:

Application.Calculation = xlCalculationAutomatic

Ça devrait déjà aller un peu plus vite.
Cordialement.

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Bonjour,

Merci mais toutes mes macros comportent déjà la fonction Application.Calculation
De même pour Application.ScreenUpdating = False.
Si vous avez d'autres idées........
Cordialement

Schlew

 

[Modeste geedee]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Bonsour®

Bonjour,

Merci mais toutes mes macros comportent déjà la fonction Application.Calculation
De même pour Application.ScreenUpdating = False.
Si vous avez d'autres idées........
Cordialement

Schlew

quand on fait des boucles pour supprimer des lignes,
il est préférable d'utiliser des boucles décroissantes c-a-d commencer par la fin.
For i = (ancienb - 2) to 1 step-1

eviter les select
Rows("33:33").Select
Selection.Delete Shift:=xlUp
devient :
Rows("33:33").Delete Shift:=xlUp

de même:
Rows("32:32").Select
Selection.Copy
Rows(ligne).Select
ActiveSheet.Paste
devient
Rows("32:32").Copy
Rows(ligne).Paste

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Merci Modeste geedee.

C'est un peu mieux mais pas aussi rapide qu'avec les 4 fichiers ouverts. (je dois gagné 2 sec. sur les trentes; )
Et il n'aime pas Rows(ligne).Paste (je ne sais pas pourquoi).
Je tiendrai compte de tes changements dans le reste de mes macros.
Mais mon pb reste d'actualité.....

Schlew

 

[MJ13]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Bonjour à tous

Avec les 4 fichiers ouverts: 2 secondes

Le mieux serait d'ouvrir les fichiers, surtout si ils sont petits, c'est très rapide.

Mais j'ai l'impression que tu travailles comme sur des fichiers partagés. Après il faut voir ce que tu traites comme informations. En l'état,c'est pas clair du tout .

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Oui, c'est sur que ce serait mieux de pouvoir ouvrir ces fichiers.
Seulement, plusieurs personnes peuvent travailler dessus en même temps et ouvrir ces 4 fichiers à chaque fois que je veux saisir mes résultats est assez contraignant.
Le fichier de saisie des résultats va chercher des informations que mes techniciens enregistrent dans les autres fichiers.
Exemple: j'ai un échantillon A, la feuille de calcul qui contient la macro va cherché par exemple la prise d'essai de cet échantillon A dans le FICHIERB, d'autres informations dans le FICHIERC et ainsi de suite. En gros, ma ligne de calcul contient 63 colonnes dont 12 qui sont liés aux FICHIER B, C, D et E.

Personne ne connaitrait une autre méthode pour mettre en cache les fichiers liés?

Schlew

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

J'ai essayé avec cette macro:

Sub Test5()
Dim Cn As ADODB.Connection
Dim Fichier As String

'Définit le classeur fermé servant de base de données
Fichier = "Q:\FICHIERB.xls"

Set Cn = New ADODB.Connection

'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Fichier & _
";Extended Properties=Excel 8.0;"
.Open
End With

'Extended Properties=Excel 8.0 est utilisé pour les versions d'Excel 97, 2000 et 2002.

'
'... la requête ...
'

'--- Fermeture connexion ---
Cn.Close
Set Cn = Nothing
End Sub

MAis il me met une erreur de complation à la premier ligne: Type défini par l'utilisateur non défini.....

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Mouaip!!!! Probleme de référence pour l'erreur et me demande toujours où et le fichier FICHIERB.
C'est donc pas une solution........

 

[MJ13]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

Re

En fait ce que tu demandes est une des choses les plus difficiles à faire et n'est pas le point fort d'Excel mais plutôt de véritables SGBD. En plus si tu n'y connaîs pas gand chose en Excel et en VBA, cela me paraît difficile à moins de suivre une formation sur ce sujet.

Et ne peux tu pas mettre toutes tes colonnes sur une seule feuille? Ainsi, cela serait plus facile de faire des connexions de type ODBC (ou SQL).

Mais cela ressemble à un classeur partagé, ce que tu veux faire. As tu dèjà essayé?

 

[Schlew]

Re : Problème de lenteur de macros; cause fichiers liés fermés!!!! HELP

C'est sur qu'avec une base de donnée ce serait plus simple.
Mettre toutes les colonnes dans une seule feuille va être trop difficile à gérer. JE peux par contre mettre les 4 fichiers dans 1 seul fichier avec 4 feuilles. Mais je ne sais pas si ça va résoudre le pb.
En fait le problème vient de notre réseau. Si je mets tout les fichiers sur une clé USB, la macro mets 4 secondes à ce faire avec les fichiers fermés.
Je suis pas sur de trouver une autre solution que d'attendre la mise en place d'un bon réseau........
Sauf si.... quelqu'un touve......

Merci pour votre aide à tous en tout cas.

Merci à tous pour votre aide