Impression PDF

Le 02/12/2014 à 08:30

Bonjour au forum,

J'ai un fichier excel avec 2 feuilles : 1 - Template | 2 - Fournisseur. (Fichier en pièce jointe).

Mon bouton imprimer en feuille 1, lance l'impression sur mon imprimante par défaut qui celle du réseau d'entreprise.

1 - Je cherche à rajouter dans la macro que l'imprimante soit PDFcreator quand je clique sur le bouton imprimer.

2 - Qu'il enregistre mon pdf dans le dossier mes documents

3 - Que le fichier pdf porte le nom en fonction de la valeur de ma cellule F3 de ma feuille Template

4 - Qu'il imprime en premier le template avec les informations du fournisseur 1, puis fournisseur 2, puis fournisseur 3 ... ETC

'Cette fonction marche déjà avec mon bouton imprimer'

Merci d'avance de vos pistes & de votre aide.

18impression-fournisseur.xlsm (18.56 Ko)

Le 02/12/2014 à 10:12

Bonjour,

A tester, mais nul besoin de PDFcreator.

Option Explicit
Option Private Module
Public Sub IMPFRS()
Dim ws As Worksheet
Dim rng As Range
Dim i As Byte
Dim Filename As String, myFolder As String

    Application.ScreenUpdating = False
    myFolder = Application.DefaultFilePath & Application.PathSeparator
    Set ws = Worksheets("Template")

    With ws
        Set rng = .Range("A2:G50")
        For i = 1 To 3
            .[A1] = i
            rng.PrintOut
            Filename = .[F3]
            rng.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    Filename:=myFolder & Filename, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=True, _
                    From:=1, _
                    To:=1, _
                    OpenAfterPublish:=False
        Next i
        .[A1] = ""
    End With

    Set rng = Nothing
    Set ws = Nothing

End Sub

Le 02/12/2014 à 10:39

Effectivement sans passer par PDF creator cela fonctionne aussi. Par contre je n'est pas utiliser ton code mais celui ci

Sub IMPFRS()
'
' IMPFRS Macro
'

'

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "10"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "13"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "14"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "16"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "17"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "18"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "19"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "21"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "22"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "23"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "24"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "25"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "26"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "27"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "28"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "29"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "30"
    Range("A2").Select
   ChDir "Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[F3].Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

Il est long est répétitif mais il marche à la perfection (Il est adapté à mon fichier de travail & non à l'exemple en pièce jointe)

Est-il possible de réduire le code ?

Merci

Le 02/12/2014 à 11:11

Re,

As-tu réellement regardé ce que j'ai envoyé?

Essaie en modifiant ma proposition ainsi :

For i = 1 To 30

myFolder ="Z:\06 - Budget - Reporting - Indicateurs\02- Reporting mensuel\PDF RELANCE 2013"

Le 02/12/2014 à 11:20

Je viens de tester ton code. Il n'imprime pas en PDF, il envoi les impressions sur le copieur réseau.

Je regarde pour intégrer des morceaux de ton code dans le miens.

Le 02/12/2014 à 11:45

Re,

La proposition initiale enregistre 3 fichiers au format pdf dans le chemin que tu indiquais, soit :

c:\users\xxx\Mes documents.

Sinon, modifie Filename ainsi (sans grande conviction)

Filename = .[F3] & "pdf"

nota : La méthode 'ExportAsFixedFormat' réalise une exportation dans un fichier avec le format spécifié. Elle n'imprime pas!

Rechercher des sujets similaires à "impression pdf"