Hello everybody,
Here is where I am there: I inserted the file Template as object in the file Creation and I coded the button "Bt_Create" as follows:
Private Sub Bt_Create_Click()
Dim chemin As String
Dim objShell As Object, objFolder As Object, oFolderItem As Object
Dim i As Integer
Dim fich As String, file As String, dep As String
Dim wb As Workbook
Dim Cel As Range
Dim Depart As Long
Dim FName As String, mPath
Dim obj
Application.ScreenUpdating = False
mPath = "C:\My Documents\"
For Each obj In Worksheets(2).OLEObjects
If obj.Name = "TEST" Then
Application.EnableEvents = False
obj.Verb
obj.Object.Activate
obj.Object.SaveAs mPath & "TEST_success.xlsm"
obj.Object.Close
End If
i = i + 1
Next
FName = BrowseFolder("Select A Folder")
If Dir(FName, vbDirectory) <> vbNullString Then
chemin = FName
file = "C:\Mon Espace Disque\HSE\4- 2016\TEST_success.xlsm"
For i = 0 To LBFichier.ListCount - 1
Set Cel = Sheets("ListeCode").Columns("B").Find(what:=Me.LBFichier.List(i, 0), LookIn:=xlValues, lookat:=xlWhole)
If Not Cel Is Nothing Then
Depart = Cel.Row
Sheets("ListeCode").Range("C" & Depart).Value = Sheets("ListeCode").Range("C" & Depart).Value + 1
dep = Sheets("ListeCode").Range("D" & Depart).Value
End If
fich = Me.LBFichier.List(i, 0) & " (" & dep & ")"
Set wb = Workbooks.Open(file)
wb.Worksheets("Liste par section").Range("B1").Value = Me.LBFichier.List(i, 0)
wb.Worksheets("Liste par section").Range("B2").Value = dep
ActiveWorkbook.SaveAs Filename:=chemin & fich
wb.Close
Set Cel = Nothing
Application.EnableEvents = True
Next i
LBFichier.Clear
MsgBox "Everything is ok"
End If
Kill "C:\My Documents\TEST_success.xlsm"
Application.ScreenUpdating = True
End Sub
This code works more or less in mode step by step...
In automatic, several problems appear:
1. During the protection(saving) of the file Template
2. On the line "Set wb = Workbooks. Open", it does not find that wb is the file Template
If one of you could have a look on my code, he would know about it very grateful
Good day,
Friendly