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
| Public Sub RenameDoc()
Dim GoodName As String
Dim GoodfullName As String
Dim BadFullName As String
With Worksheets("REQUEST")
'Concatenate the name of the project
.Cells(17, 18).Value = .Cells(15, 2).Value + " " + .Cells(15, 5).Value + " " + .Cells(15, 7).Value + " " + .Cells(15, 11).Value + " " + .Cells(15, 15).Value + " " + .Cells(15, 20).Value
End With
'If Worksheets("REQUEST").Cells(4, 7).Value <> "" Then
'New standard name
GoodName = Format(Date, "yyyymmdd") & " DocReq " & Worksheets("REQUEST").Cells(17, 18).Value & " " & Worksheets("REQUEST").Cells(4, 7).Value & " " & Worksheets("REQUEST").Cells(6, 7).Value & ".xlsm"
'New name with MSwindows path
GoodfullName = Replace(ActiveWorkbook.FullName, ActiveWorkbook.name, GoodName)
'Curent file name with path
BadFullName = ActiveWorkbook.FullName
'save curent file with new name
ActiveWorkbook.SaveAs (GoodfullName)
'Open file with new name
Workbooks.Open Filename:=GoodfullName
'Close file with old name, without saving it
Workbooks(BadFullName).Close SaveChanges:=False
'End If
End Sub |
Partager