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
| Option Explicit
Sub SauvegardeMacros()
Dim AWbk As Workbook
Dim DateEtHeure As String
Dim NomSansExt As String
Dim DossierSauvegarde As String
Set AWbk = ActiveWorkbook
' Activation de la référence
' "Microsoft Visual Basic for Applications Extensibility"
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=0, Minor:=0
On Error GoTo 0
DateEtHeure = "-" & Format(Now, "dd-mm-yy hh-mm-ss")
NomSansExt = Mid(AWbk.Name, 1, InStr(1, AWbk.Name, ".") - 1)
DossierSauvegarde = AWbk.Path & Application.PathSeparator & "Code " & NomSansExt & DateEtHeure
'Exportation des modules
ExportAllVBA AWbk.Name, DossierSauvegarde
If MsgBox("Ouvrir le dossier de sauvegarde ?", vbYesNo) = vbYes Then _
Shell "C:\WINDOWS\EXPLORER.EXE /n,/e," & DossierSauvegarde, vbNormalFocus
End Sub
Sub ExportAllVBA(Quoi, Destination)
'macro d'origine de Chip pearson
'http://www.cpearson.com/excel/vbe.aspx
Dim VBComp As VBIDE.VBComponent
Dim Ext As String
Dim DossierSauvegarde As String
Dim Wbk As Workbook
Dim Dest As String
'Création des dossiers de destination
DossierSauvegarde = Destination
MkDir DossierSauvegarde
MkDir DossierSauvegarde & Application.PathSeparator & "Modules de feuille"
'export des codes
Set Wbk = Workbooks(Quoi)
For Each VBComp In Wbk.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule
Ext = ".cls": Dest = Destination
Case vbext_ct_MSForm
Ext = ".frm": Dest = Destination
Case vbext_ct_StdModule
Ext = ".bas": Dest = Destination
Case vbext_ct_Document
Ext = ".cls": Dest = Destination & Application.PathSeparator & "Modules de feuille"
Case Else
Ext = ""
End Select
If Ext <> "" Then
VBComp.Export _
Filename:=Dest & Application.PathSeparator & VBComp.Name & Ext
Dest = ""
End If
Next VBComp
End Sub |
Partager