Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Sub CommandButton1_Click()
Dim i&, Rcount&, Ccount&, Place As Range, Chemin$, objFeuilPass As Worksheet, coeffzoom#
If (Me.Width / 28.3464567) > 21 Then coeffzoom = ((Me.Width / 28.3464567) / 21) - 0.2 Else coeffzoom = 0
'Supprimer/création feuille "Temporaire"
With Application
.DisplayAlerts = False: .ScreenUpdating = False
On Error Resume Next
Sheets("Temporaire").Delete
Err.Clear
Sheets.Add.Name = "Temporaire": Set objFeuilPass = Sheets("Temporaire")
End With
'calcul du nombre de ligne et colonne de la plage pouvant contenir le cliché du userform
Rcount = Round(2 + (Me.Height + (Me.Height - Me.InsideHeight)) / 15)
Ccount = Round(0.5 + (Me.Width / 60))
For i = 0 To MultiPage1.Pages.Count - 1
MultiPage1.Value = i
Set Place = [A1].Resize(Rcount, Ccount).Offset(Rcount * i)
Me.Repaint
Sleep (50)
'capture écran en simulant l'appui et la relache du bouton Imprécran
keybd_event vbKeySnapshot, &H1, &H0, &H0: DoEvents: Sleep (50): keybd_event vbKeySnapshot, 0&, &H2, 0&
Application.Wait Now + TimeValue("0:00:01") 'pour ca il y a d'autre moyen mais je te le laisse
objFeuilPass.Paste ' colle le Snapshot
With objFeuilPass.Shapes(i + 1) 'place l'image au centre de la (range ligne/colonne calculées)
.Top = Place.Top + ((Place.Height - .Height) / 2)
.Left = Place.Left + ((Place.Width - .Width) / 2)
End With
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells((Rcount * i) + 1, 1) 'ajoute un saut de page 1 ligne apres la place
Next i
With ActiveSheet.PageSetup
.PrintArea = [A1].Resize(Place.Cells(Place.Cells.Count).Row, Ccount).Address 'defini l'area global pour le print
.CenterHorizontally = True: .CenterVertically = True 'imprime au centre de la page
.Orientation = IIf(Me.Width > Me.Height, xlLandscape, xlPortrait) 'defini l'orientation selon la forme du userform
.LeftMargin = 0: .RightMargin = 0: .TopMargin = 0: .BottomMargin = 0 ' pas de marge
.Zoom = 100 / coeffzoom
End With
Chemin = Environ("userprofile") & "\Desktop\userform multip.pdf" ' chemin de sauvegarde du pdf
With Sheets("Temporaire")
'export du sheet Temporaire en pdf
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Chemin, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, From:=1, To:=Me.MultiPage1.Pages.Count, OpenAfterPublish:=True
.Delete 'suppression du sheet temporaire
End With
ActiveWindow.SelectedSheets.VPageBreaks(1).Delete ' au cas ou l'arrondi du calcule donnerait une colonne en moins de largeur que la largeur de l'userform
Application.DisplayAlerts = True
End Sub
Bonjour forum,
J'ai une version excel 97-2003
Chemin = Environ("userprofile") & "\Desktop\userform multip.pdf" ' chemin de sauvegarde du pdf
With Sheets("Temporaire")
'export du sheet Temporaire en pdf
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Chemin, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, From:=1, To:=Me.MultiPage1.Pages.Count, OpenAfterPublish:=True
.Delete 'suppression du sheet temporaire
End With
Worksheets("Temporaire").Activate
Application.Dialogs(xlDialogPrint).Show
Worksheets("Temporaire").Delete 'suppression du sheet temporaire
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
Private Declare Sub keybd_event Lib "User32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long
Private Declare Function EmptyClipboard Lib "User32" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As Integer) As Long
Private Sub CommandButton1_Click()
Dim i&, Rcount&, Ccount&, Place As Range, Chemin, objFeuilPass As Worksheet, coeffzoom#
If (Me.Width / 28.3464567) > 21 Then coeffzoom = ((Me.Width / 28.3464567) / 21) - 0.2 Else coeffzoom = 0 'calcul du zomm par raport au coté 21 cm d'une feuille A4
'Supprimer/création feuille "Temporaire"
With Application
.DisplayAlerts = False: .ScreenUpdating = False
On Error Resume Next
Sheets("Temporaire").Delete
Err.Clear
Sheets.Add.Name = "Temporaire": Set objFeuilPass = Sheets("Temporaire")
End With
'calcul du nombre de lignes et colonnes de la plage pouvant contenir le cliché du userform
Rcount = Round(2 + (Me.Height + (Me.Height - Me.InsideHeight)) / 15)
Ccount = Round(0.5 + (Me.Width / 60))
For i = 0 To MultiPage1.Pages.Count - 1
MultiPage1.Value = i
Set Place = [A1].Resize(Rcount, Ccount).Offset(Rcount * i)
Me.Repaint
Sleep (50)
WaitPictureClip False 'attentente de vidage du clipboard
'capture écran en simulant l'appui et la relache du bouton Imprécran
keybd_event vbKeySnapshot, &H1, &H0, &H0: DoEvents: Sleep (50): keybd_event vbKeySnapshot, 0&, &H2, 0&
'Application.Wait Now + TimeValue("0:00:01") 'pour ca il y a d'autre moyen (voir ci dessous)
WaitPictureClip True 'attente de l'image dans le clipboard
objFeuilPass.Paste ' colle le Snapshot
With objFeuilPass.Shapes(i + 1) 'place l'image au centre de la (range ligne/colonne calculées)
.Top = Place.Top + ((Place.Height - .Height) / 2)
.Left = Place.Left + ((Place.Width - .Width) / 2)
End With
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells((Rcount * i) + 1, 1) 'ajoute un saut de page 1 ligne apres la place
Next i
With ActiveSheet.PageSetup
.PrintArea = [A1].Resize(Place.Cells(Place.Cells.Count).Row, Ccount).Address 'defini l'area global pour le print
.CenterHorizontally = True: .CenterVertically = True 'imprime au centre de la page
.Orientation = IIf(Me.Width > Me.Height, xlLandscape, xlPortrait) 'defini l'orientation selon la forme du userform
.LeftMargin = 0: .RightMargin = 0: .TopMargin = 0: .BottomMargin = 0 ' pas de marge
.Zoom = 100 / coeffzoom
End With
If Val(Application.Version) >= 12 Then
'Chemin = Environ("userprofile") & "\Desktop\userform multip.pdf" ' chemin de sauvegarde du pdf
Chemin = Application.GetSaveAsFilename(InitialFileName:=Environ("userprofile") & "\Desktop", filefilter:="PDF Files (*.pdf), *.pdf", Title:="ENREGISTREMENT DU PDF")
If Chemin <> False Then
With Sheets("Temporaire")
ActiveWindow.SelectedSheets.VPageBreaks(1).Delete ' au cas ou l'arrondi du calcule donnerait une colonne en moins de largeur que la largeur de l'userform
'export du sheet Temporaire en pdf
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Chemin, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, From:=1, To:=Me.MultiPage1.Pages.Count, OpenAfterPublish:=True
End With
End If
Else
Worksheets("Temporaire").Activate
Application.Dialogs(xlDialogPrint).Show
End If
Sheets("Temporaire").Delete 'suppression du sheet temporaire
Application.DisplayAlerts = True
End Sub
Function WaitPictureClip(Optional sens As Boolean = True)
Dim v&, x&
v = Abs(sens): OpenClipboard 0: If v = 0 Then EmptyClipboard
x = IsClipboardFormatAvailable(2) '2=bitmap
Do While x <> v: x = IsClipboardFormatAvailable(2): DoEvents: Loop
CloseClipboard
WaitPictureClip = x
End Function