1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
| Option Explicit
Private Declare Function SHCreateDirectoryEx Lib "Shell32.dll" Alias "SHCreateDirectoryExA" _
(ByVal hwnd As Long, ByVal pszPath As String, ByVal lngsec As Long) As Long
Dim Ar() As String
Private Function CreationDossier(sDossier) As Long
Dim Rep As Long
Rep = SHCreateDirectoryEx(0&, sDossier, 0&)
End Function
Private Function DecompteA() As Boolean
Dim LastRow As Long, i As Long
DecompteA = False
With shRecap
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If UCase$(.Cells(i, 2)) = "X" Then
DecompteA = True
Exit Function
End If
Next i
End With
End Function
Sub EnregistrementPDF()
Dim sNomFichierPDF As String
Dim i As Long, cpt As Long, LastRow As Long
Dim FSO As Object, sNomfichier As String, sFichierPDF As String
Dim sRacine As String, sDossierPDFs As String, sNomFichierFusion As String
sRacine = ThisWorkbook.Path
sDossierPDFs = "Dossier PDFs"
If DecompteA = False Then
MsgBox "Taper dans la colonne B un x ou X en vis à vis" & vbCrLf & _
"des Feuilles à exporter en PDF" & vbCrLf & _
"Ou ..... lancer la Récap", vbInformation + vbOKOnly, "x ou X"
Exit Sub
End If
With Application
.ScreenUpdating = False
.StatusBar = ""
End With
If shRecap.CheckBoxes("chkPDF").Value = 1 Then
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(sRacine & "\" & sDossierPDFs) Then _
FSO.DeleteFolder sRacine & "\" & sDossierPDFs, True
Set FSO = Nothing
shRecap.CheckBoxes("chkDoublons").Value = 0
End If
CreationDossier sRacine & "\" & sDossierPDFs
LastRow = shRecap.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If UCase$(shRecap.Cells(i, 2)) = "X" Then
cpt = cpt + 1
sNomFichierPDF = sRacine & "\" & sDossierPDFs & "\" & shRecap.Cells(i, 1)
If shRecap.CheckBoxes("chkFusion").Value = 1 Then
If shRecap.CheckBoxes("chkDoublons").Value = 1 Then
sNomFichierFusion = "Fusion.pdf"
sNomfichier = RenommerFichier(sRacine & "\" & sDossierPDFs, sNomFichierFusion)
Else
sNomFichierFusion = sRacine & "\" & sDossierPDFs & "\" & "Fusion.pdf"
sNomfichier = sNomFichierFusion
End If
Application.ScreenUpdating = False
Fusion
Sheets(Ar).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=sNomfichier, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.ScreenUpdating = True
shRecap.Select
Exit For
Else
If shRecap.CheckBoxes("chkDoublons").Value = 1 Then
sFichierPDF = shRecap.Cells(i, 1) & ".pdf"
sNomfichier = RenommerFichier(sRacine & "\" & sDossierPDFs, sFichierPDF)
Else
sNomfichier = sNomFichierPDF
End If
Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=sNomfichier, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
Application.StatusBar = cpt
End If
Next i
With shRecap
.Activate
.Range("C1").Select
End With
Application.ScreenUpdating = True
End Sub
Private Sub Fusion()
Dim i As Long, j As Long, LastRow As Long
LastRow = shRecap.Range("A" & Rows.Count).End(xlUp).Row
Erase Ar(): j = 0
For i = 1 To LastRow
If UCase$(shRecap.Cells(i, 2)) = "X" Then
ReDim Preserve Ar(j)
Ar(j) = Sheets(i).Name
j = j + 1
End If
Next i
End Sub
Private Sub PosBoutons()
Dim T As Range
With shRecap
.Activate
Set T = .Cells(2, 4)
With .Buttons("BtnRecap")
.Left = T.Left + 5
.Top = T.Top
.Width = 50
End With
With .Buttons("BtnPdf")
.Left = shRecap.Buttons("BtnRecap").Left
.Top = shRecap.Buttons("BtnRecap").Top + shRecap.Buttons("BtnRecap").Height + 3
.Height = shRecap.Buttons("BtnRecap").Height
.Width = shRecap.Buttons("BtnRecap").Width
End With
Set T = .Cells(2, 5)
With .Shapes("chkPDF")
.Left = T.Left
.Top = T.Top
.Width = 145
.Height = shRecap.Buttons("btnPDF").Height / 2 - 10
End With
With .Shapes("chkDoublons")
.Left = shRecap.Shapes("chkPdf").Left
.Top = shRecap.Shapes("chkPdf").Top + shRecap.Shapes("chkPdf").Height + 2
.Width = shRecap.Shapes("chkPdf").Width
.Height = shRecap.Buttons("btnPDF").Height / 2 - 10
End With
With .Shapes("chkFusion")
.Left = shRecap.Shapes("chkPdf").Left
.Top = shRecap.Shapes("chkDoublons").Top + shRecap.Shapes("chkDoublons").Height + 2
.Width = shRecap.Shapes("chkPdf").Width
.Height = shRecap.Buttons("btnPDF").Height / 2 - 10
End With
End With
Set T = Nothing
End Sub
Sub Recap()
Dim i As Long
Application.ScreenUpdating = False
shRecap.Columns("A:B").ClearContents
For i = 1 To ThisWorkbook.Sheets.Count
shRecap.Cells(i, 1) = Sheets(i).Name
Next i
shRecap.Columns("A:B").Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Private Function RenommerFichier(sDossier As String, sNomfichier As String) As String
Dim sNouveauNom As String
Dim sPre As String
Dim sExt As String
Dim iExt As Long
Dim i As Long, Pos As Long
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(sDossier & "\" & sNomfichier) Then
sNouveauNom = sNomfichier
Pos = InStrRev(sNomfichier, ".")
If Pos > 0 Then
iExt = Len(sNomfichier) - Pos + 1
sExt = Right$(sNomfichier, iExt)
sPre = Left$(sNomfichier, Len(sNomfichier) - iExt)
Else
sExt = ""
sPre = sNomfichier
End If
i = 0
While FSO.FileExists(sDossier & "\" & sNouveauNom)
i = i + 1
sNouveauNom = sPre & Chr(40) & Format(i, "000") & Chr(41) & sExt
Wend
sNomfichier = sNouveauNom
End If
Set FSO = Nothing
RenommerFichier = sDossier & "\" & sNomfichier
End Function |
Partager