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
|
Sub creationModule()
'Nécéssite d'activer la référence
'"Visual basic For Application Extensibility 5.3"
'
Dim Wb As String
Dim VBComp As Object
Dim X As Integer
' Application.ScreenUpdating = False
'Définit le classeur cible
' Set Wb = Workbooks.Open("C:\Users\212463909\Desktop\test.xls")
'----------------------------------------------------------------
Dim Row As Range
For Each Row In Range("A1:A3") '<<< CHANGE
If Len(Row.Text) > 0 Then
Set Wb = Workbooks.Open(Row, 1)
'----------------------------------------------------------------
'Ajoute un module standard dans le classeur
Set VBComp = Wb.VBProject.VBComponents.Add(1)
'Renomme le module
VBComp.Name = "Module1"
'Ajoute une macro dans le module
With VBComp.CodeModule
X = .CountOfLines
.InsertLines X + 1, "Sub Bouton_Copie()"
.InsertLines X + 2, " Dim strDate As String"
.InsertLines X + 3, "Dim iptResult As String"
.InsertLines X + 4, " Cells.Copy"
.InsertLines X + 5, " Sheets.Add before:=ActiveSheet"
.InsertLines X + 6, " strDate = InputBox(""Inserer la date dans le format JJ-MM-AAAA"", ""User date"", Format(Date, ""dd-MM-yyyy""))"
.InsertLines X + 7, " If IsDate(strDate) Then"
.InsertLines X + 8, " strDate = Format(CDate(strDate), ""yyyy-MM-dd"")"
.InsertLines X + 9, " ' MsgBox strDate"
.InsertLines X + 10, " Else"
.InsertLines X + 11, " MsgBox ""Mauvais format de date"""
.InsertLines X + 12, " strDate = InputBox(""Inserer la date dans le format JJ-MM-AAAA"", ""User date"", Format(Date, ""dd-MM-yyyy""))"
.InsertLines X + 13, " End If"
.InsertLines X + 14, " ActiveSheet.Name = strDate"
.InsertLines X + 15, " ActiveSheet.Paste"
.InsertLines X + 16, " ActiveSheet.Range(""a1"").Select"
.InsertLines X + 17, " Application.CutCopyMode = False"
.InsertLines X + 18, "End Sub"
End With
'Sauvegarde les modifications
Wb.Save
Wb.Close
On Error Resume Next
On Error GoTo 0
End If
Next Row
End Sub |
Partager