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
| Sub Create_Folder()
Dim File_Src As Workbook
Dim File_Dest As Workbook
Dim RFS As Worksheet
Dim Insp_Date As Range
Dim Data_Clear As Range
Dim No As Range
Dim File_Path As String
Dim File_Name As String
Dim Wk_Name As String
Dim Folder_Name As String
Set RFS = ThisWorkbook.Sheets("RFS")
Set Insp_Date = RFS.Range("S26")
Set No_File = RFS.Range("R24")
Set Data_Clear = RFS.Range("C48")
Const Cible = "Z:\009 QUALITY\Service Ticket Folder\Request for Services Estimate"
Dim BV_Shell As Object
Dim BV_Folder As Object, BV_FolderItem As Object
Set BV_Shell = CreateObject("Shell.Application")
Set BV_Folder = BV_Shell.Namespace(Cible)
Set BV_FolderItem = BV_Folder.Self
File_Path = Cible
File_Name = Format(Insp_Date, "mmm_yyyy") & " " & Service_Request.ComboBox1
Folder_Name = File_Path & Application.PathSeparator & File_Name
Wk_Name = "Request for Service Estimate" & " " & No_File.Value & ".xls"
If Dir(Folder_Name, vbDirectory) = "" Then
MkDir Folder_Name
End If
RFS.Copy
'With ActiveWorkbook
' .SaveAs Filename:=Folder_Name & Application.PathSeparator & Wk_Name
' .Close
'End With
ActiveWorkbook.SaveAs Folder_Name & Application.PathSeparator & Wk_Name
'File_Dest.SaveAs Filename:=Wk_Name
'FileCopy Wk_Name, "Z:\009 QUALITY\Service Ticket Folder\Request for Services Estimate"
'File_Dest.SaveAs File_Path & "\" & File_Name & "\" & Wk_Name & ".xls"
ActiveWorkbook.Close
Range(RFS.Range("C48"), RFS.Range("C48").End(xlToRight).End(xlDown)).ClearContents
Range(Data_Clear.Offset(0, 4), Data_Clear.Offset(0, 4).End(xlToRight).End(xlDown)).ClearContents
Range(Data_Clear.Offset(0, 8), Data_Clear.Offset(0, 8).End(xlToRight).End(xlDown)).ClearContents
Range(Data_Clear.Offset(0, 13), Data_Clear.Offset(0, 13).End(xlToRight).End(xlDown)).ClearContents
Range(Data_Clear.Offset(0, 19), Data_Clear.Offset(0, 19).End(xlToRight).End(xlDown)).ClearContents
'Range(Data_Clear.Offset(0, 22), Data_Clear.Offset(0, 22).End(xlToRight).End(xlDown)).ClearContents
End Sub
Sub CopyRFS()
Dim Folder_Name As String, Wk_Name As String
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("RFS")
Folder_Name = ThisWorkbook.Path & "\" & Format(.Range("S26"), "mmm_yyyy") & "\"
If Dir(Folder_Name, vbDirectory) = "" Then MkDir Folder_Name
Wk_Name = Folder_Name & "Request for Service Estimate " & .Range("R24") & ".xls"
.Copy
End With
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=Wk_Name
.Close
End With
Application.DisplayAlerts = True
End Sub |
Partager