XL 2010 Macro génération PDF à la suite

[safranien]

Bonjour à tous,

je dispose d'un fichier de travail assez lourd qui doit être utilisé en mode de calcul manuel. Le principe est : choix d'un site via un menu déroulant, clic sur un bouton "Lancer les calculs" qui exécute une macro combinant des formules dans la macro et des actualisation de formules de calculs présentes dans des cellules de plusieurs feuilles. Un bouton PDF permet de générer un PDF de la feuille (plusieurs en fait). Lorsque le PDF est généré, ce dernier s'ouvre. Je passe au site suivant via le menu déroulant etc

J'aimerais réussir à automatiser la génération des PDF, un après l'autre, de tous les sites présents dans le menu déroulant. Dans mon fichier de travail, la macro "Lancer les calculs" est assez longue à s'exécuter (environ 30 secondes) contrairement au fichier exemple joint. Dans ledit fichier, j'ai essayé de reproduire simplement le principe de fonctionnement.
J'aimerais donc qu'une macro inscrive le premier nom de site de la liste déroulante (colonne O de l'onglet BDD) en H2 de l'onglet BDD, exécuter la macro "Lancer les calculs", exécuter une autre macro de mise en PDF mais sans ouverture de ce dernier une fois créé, inscrire le nom de site suivant en H2 etc etc Et lorsqu'on arrive au bout de la liste, effacer le contenu de H2, lancer une dernière fois la macro "Lancer les calculs" et afficher une boite de dialogue pour dire que tous les PDF sont générés.
Etant donné que ma macro Lancer les Calculs de mon fichier de travail met du temps à s'exécuter, faut il insérer un temps de pause entre Lancer les Caluls et la mise en PDF ?

Question bonus : j'oblige les utilisateur à fermer tous leurs excel avant d'ouvrir ce fichier assez lourd et que ce dernier ne bascule pas en calcul automatique à son ouverture. Existe-t-il un moyen, lors de l'ouverture de mon fichier, de vérifier si d'autres fichiers excel sont ouverts et si oui, qu'un message s'affiche en disant "Merci de fermer vos autres fichiers avant d'ouvrir celui-ci" ? Ou alors forcer l'ouverture en mode de calcul manuel même si d'autres fichiers en calcul automatique sont ouverts ?

En espérant que vous pourrez m'aider.

Merci

 

[Phil69970]

Bonjour Safranien, le forum

Je te propose un début de piste....
Ce code remplace Call Calculs et simplifie grandement ton code "Tout"

Sub Tout()

Dim Derlig&
Derlig = Worksheets("BDD").Range("O" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To Derlig
    With Sheets("BDD")
        .Range("H2") = .Range("O" & i)
    End With
       
    With Sheets("Graphs")
         .Calculate 'Lance le calcul sur la feuille Graphs du classeur
        .Range("B12:G21").ClearContents
       
        .Range("B12:G21") = .Range("B9")    
         Application.CutCopyMode = False
      
    End With
   
    PDF_2
Next i

MsgBox "La génération de tous les PDF est terminée"

Sheets("BDD").Range("H2").ClearContents

End Sub

Calcul manuel :
Application.Calculation = xlManual

Calcul automatique :
Application.Calculation = xlAutomatic

*Ne pas oublier de remettre le calcul comme il était après l'opération.

Perso : Demander à l'utilisateur de fermer ses fichiers excel pour travailler avec le tien ne me semble pas top.

@Phil69970

 

[safranien]

Bonjour Phil69970

merci beaucoup pour ton retour. Je suis obligé d'appeler la procédure Calculs dans mon fichier d'origine, le code étant assez long et complexe. J'ai donc modifié en ce sens ton code et ça fonctionne dans mon fichier test. Mais dans mon fichier de travail, ça me génère le 1er premier PDF et après le fichier reste bloqué. Obligé de faire ctrl+alt+supp. J'essaie de trouver ce qui provoque cela.

 

[Phil69970]

Bonjour Safranien, le forum

Si tu ne nous donnes pas un exemple de fichier qui provoque le même problème que chez toi ça va être dur de t’aider.

@Phil69970

 

[safranien]

Oui mais mon fichier fait 24 Mo et fonctionne si il est connecté via mon réseau d'entreprise pour récupérer des données via PI Data Link. C'est pour cela que j'ai essayé de faire un fichier exemple qui se rapproche de l'esprit de mon fichier de travail.
Ma colonne de nom de site fait 350 lignes. Quand je fais tourner ton code en supprimant Application.ScreenUpdating = False, je vois que le fichier reste un moment sur le dernier nom du site et que ça clignote toutes les secondes jusqu'à arriver au bout de l'opération et obtenir la messagebox. On dirait que la macro passe recopie toutes les cellules l'une après l'autre, même celle étant vides (elles sont vides mais contiennent des formules qui renvoient "").
L'idée est bien de détecter dans cette colonne les cellules ayant des valeurs texte et de n'utiliser que celles ci pour les recopier une à une vers une autre cellule, l'une après l'autre. Est-ce que ton code fait bien abstraction des cellules vides et celles contenant une formule renvoyant "" ?

 

[Phil69970]

Re
Mon code fait exactement ce que faisait ta macro posté #1 ni plus ni moins.
Il fait cela en plus court et plus lisible.

Tu peux regarder en plaçant des points d'arret dans le code vba et/ou en lancant le code ligne à ligne (Touche F8) pour voir ce qui se passe.

Une piste :

Application.CalculationState, propriété

Cette propriété renvoie une constante XlCalculationState qui indique l'état de calcul de l'application, pour les calculs en cours dans Microsoft Excel. Type de données en lecture seule.

L'aide en ligne me donne cela comme exemple :

If Application.CalculationState = xlDone Then
    MsgBox "Done"
Else
    MsgBox "Not Done"
End If

Dans le même registre j'ai trouvé ça :

Application.Calculate
If Not Application.CalculationState = xlDone Then
    DoEvents
End If
'==> Reste du code

@Phil69970

 

[safranien]

Merci pour ces compléments. Le problème doit venir de ma macro de Calculs certainement alors. Je cherche.

 

[safranien]

Bonjour Phil69970
j'ai réussi à faire tourner le code et ai pu générer 15 mises en PDF à la suite. Le problème, je pense, provient bien de mon code Calculs qui est le reflet de quelqu'un qui ne sait pas coder et qui est plein de .copy .paste. J'ai ajouté un ligne Application.CutCopyMode = False à la fin de l'exécution de mon code Calculs et c'est à partir de là que tout a fonctionné. A noter que mes PDF font une quinzaine de page et sont composés de graphiques assez chargés. Bien que la mise en PDF soit paramétrée en qualité basse, un PDF peut aller jusqu'à 10 Mo.
Cependant, je rencontre toujours un problème lorsque j'arrive au dernier site de la liste. En effet, je confirme que ton code vient me copier le contenu des cellules qui sont vides en affichage mais qui en fait contiennent des formules qui renvoient "". Est-il possible que ton code exclue ces cellules et s'arrête vraiment à la dernière cellule qui retourne une valeur autre que vide "" ?

 

[Staple1600]

Bonjour le fil

Sur la base du code de Phil69970, j'ai fait ces modifs
(NB: C'est juste comment je l'écrirai à ma sauce. Sans plus)
Je n'ai pas testé

Sub Tout_bis()
Dim Derlig&
Derlig = Worksheets("BDD").Range("O" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To Derlig
Sheets("BDD").[H2] = Sheets("BDD").Range("O" & i)
Sheets("Graphs").Range("A7:B9").Calculate
Sheets("Graphs").[B12:G21] = Sheets("Graphs").[B9]
PDF_2
Next i
MsgBox "La génération de tous les PDF est terminée"
Sheets("BDD").[H2]=""
End Sub

Explications du pourquoi du comment
Comme on ne fait pas de copier/coller
j'ai effacé la ligne
Application.CutCopyMode = False
Ensuite j'ai zappé le ClearContents puisque juste après les cellules effacées prennent la valeur de B9
Enfin, je n'applique le Calculate que sur une plage restreinte.

Je ne sais pas si cela change quelque chose à l'exécution.
(en terme de rapidité)

Je vous laisse tester.

 

[safranien]

Bonjour Staple1600
merci pour ton aide également. En fait mon souci est toujours le même. Ci-joint le fichier corrigé plus en rapport avec mon fichier de travail. La valeur des cellules de la colonne O de la feuille BDD est directement mise dans la cellule A1 de la feuille Graphs.
En exécutant la macro Tout, on voit que les sites de la colonne O de la feuille BDD défilent bien dans la feuille Graphs avec les calculs associés. Là où j'aimerais que la macro s'arrête lorsqu'elle atteint le dernier nom de site, à savoir "Site 14", elle continue à reporter le contenu des cellules suivantes ce qui, dans mon fichier de travail, me fait tout planter car il n'y a pas de texte.

 

[Staple1600]

Re

=>safranien
Avant de te répondre, tu peux me dire ce que donne le test de la macro dans le message#9
(histoire que je ne l'ai pas postée pour rien )

 

[safranien]

Pardon. Elle fonctionne et donne le même résultat. Tous les sites défilent bien et les PDF sont bien générés. Mais le code reporte toujours les cellules de la colonne O qui retournent un vide via leur formule dans la cellule A1 de l'onglet Graphs. Dans mon fichier officiel, je ne peux pas avoir A1 vide sous peine de plantage de mon autre macro de Calculs.
Ci-joint le fichier modifié avec ton code et celui de Phil69970.

 

[Phil69970]

Bonjour @safranien, @Staple1600, le forum

1)Je ne comprends toujours pas pourquoi ta sub "Calculs" est sorti de la sub "Tout", de plus écrite comme ça ?
Pour moi ça ne sert à rien de vouloir la séparer.

2)Si j'ai bien compris ton problème tu veux que tout s’arrête des que la colonne O est vide de valeur.
Tu remplaces ces lignes de codes.

'Derlig = Worksheets("BDD").Range("O" & Rows.Count).End(xlUp).Row
Derlig = Worksheets("BDD").Range("O:O").Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Ce qui donne au final :

Sub Tout_Phil69970_Staple1600()

Dim Derlig&
Derlig = Worksheets("BDD").Range("O:O").Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Application.ScreenUpdating = False

For i = 2 To Derlig
    Sheets("Graphs").[A1] = Sheets("BDD").Range("O" & i)
    Sheets("Graphs").Range("A7:B9").Calculate
    Sheets("Graphs").[B12:G21] = Sheets("Graphs").[B9]
   
    PDF_2
Next i

MsgBox "La génération de tous les PDF est terminée"

End Sub

@Phil69970

 

[safranien]

Bonjour Phil69970

merci beaucoup beaucoup beaucoup pour ton retour. Ca fonctionne.

J'ai maintenant un problème de ressources, au bout du 10e PDF, j'obtiens un joli "excel ne peut pas terminer cette tâche avec les ressources disponibles" donc faut que je trouve maintenant comment régler ça. Bien que j'ai ajouté "Application.CutCopyMode = False" au début et à la fin de ma Sub Calculs. Je pense à un problème de cache du logiciel PI Data Link qui est utilisé dans mon fichier ou alors de TCD, mon fichier en ayant pas mal.

Ce n'est pas moi qui ai sorti la Sub Calculs de la Sub Tout hein ^^ Dans mon fichier de travail, je suis obligé de l'appeler et l'ai bien mise.

Je reviens sur une réponse du début du fil, à savoir forcer l'ouverture de mon fichier en mode de calcul manuel même si un autre excel en calcul automatique est déjà ouvert, à savoir inscrire Application.Calculation = xlManual. Où dois je écrire cette ligne pour qu'elle soit prise en compte dès que je lance l'ouverture de mon fichier ?

 

[Phil69970]

@safranien, @Staple1600, le forum

Ce n'est pas moi qui ai sorti la Sub Calculs de la Sub Tout hein ^^ Dans mon fichier de travail, je suis obligé de l'appeler et l'ai bien mise.

Ben si à chaque fois que tu as posté un fchier tu l'as sorti de la sub "Tout"

Donc si tu veux absolument la séparer code là comme il faut :
(Je maintiens inutile et perte de temps pour excel ou tu ne nous as pas donné la totalité du code)

Sub Calculs()

Sheets("Graphs").Range("A7:B9").Calculate
Sheets("Graphs").[B12:G21] = Sheets("Graphs").[B9]
  
End Sub

Les 2 lignes remplacent toute TA sub "Calculs"

Et comme Staple1600 te l'a dit

Comme on ne fait pas de copier/coller
j'ai effacé la ligne
Application.CutCopyMode = False

pas besoin de Application.CutCopyMode = False nulle part dans ton fichier

Si tu veux absolument désactivé le calcul automatique
Tu mets le calcul manuel à l'ouverture du fichier
ET Tu remets le calcul automatique quand tu fermes le fichier
(Comme quand tu rentres le soir dans une pièce tu allumes la lumière et que tu l'éteins quand tu en sors)

@Phil69970