As I create a project number using the below code, I need to create a folder with the title as the new project number in the following path: W:\My system\me\my work\PROJECTS\Projects\Reliability
, I know the code needs to be placed after (.Cells(MyRow, "Q").Value = Sheets("Tracker").Cells(3, "E").Value 'project NUMBER
) as title of the new project will be placed in "active column 17" once the below code is completed
So I have this code which checks if a cell is empty and when prompted to create a project number, This works fine but I'm not sure how to add code to create the new folder in the above folder
Sub MyFileprojectTF()
'Detemine to open or create report.
'Application.ScreenUpdating = False
Dim MyNewFile As String
Dim MySht, MyWBK As String
Dim MyRow As Integer
MyRow = ActiveCell.Row
MySht = ActiveSheet.Name
MyWBK = ActiveWorkbook.Name
If ActiveCell.Column = 17 Then
If ActiveCell.Value <> "" Then 'if cell in the is empty
MyFileprojectOpenTF
Else
OpenTemplate 'opens template tracker for new project number
With Workbooks("project.xls").Sheets("Tracker")
.Cells(9, "B").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "H").Value 'Project
.Cells(10, "B").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "J").Value 'Customer
.Cells(2, "G").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "P").Value 'tracker
.Cells(14, "E").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "O").Value 'tech
.Cells(15, "E").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "N").Value 'FILE REF
.Cells(25, "A").Value = Workbooks(MyWBK).Sheets(MySht).Cells(MyRow, "L").Value 'Description
End With
'***********************************
NewProjectGSRTF
UpDateMyDataBaseTF
'***********************************
With Workbooks(MyWBK).Sheets(MySht)
.Cells(MyRow, "Q").Value = Sheets("Tracker").Cells(3, "E").Value 'project NUMBER
End With
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Workbooks(MyWBK).Save
End If
End If
Application.ScreenUpdating = True
End Sub
Expanding on the two version I mentioned in comments. Update Activesheet with correct sheet and range with correct cell to collect folder name from. Currently have default "Testing"
name created in case cell is empty where getting name from.
1) MKDIR
Option Explicit
Public Sub MyFileprojectTF()
Dim startPath As String
Dim myName As String
startPath = "W:\My system\me\my work\PROJECTS\Projects\Reliability"
myName = ActiveSheet.Range("D1").Text ' Change as required to cell holding the folder title
If myName = vbNullString Then myName = "Testing"
Dim folderPathWithName As String
folderPathWithName = startPath & Application.PathSeparator & myName
If Dir(folderPathWithName, vbDirectory) = vbNullString Then
MkDir folderPathWithName
Else
MsgBox "Folder already exists"
Exit Sub
End If
End Sub
2) FSO
Option Explicit
Public Sub MyFileprojectTF()
Dim startPath As String
Dim myName As String
startPath = "W:\My system\me\my work\PROJECTS\Projects\Reliability"
myName = ActiveSheet.Range("D1").Text ' Change as required to cell holding the folder title
If myName = vbNullString Then myName = "Testing"
Dim folderPathWithName As String
folderPathWithName = startPath & Application.PathSeparator & myName
If Dir(folderPathWithName, vbDirectory) = vbNullString Then
Dim fso As Object
Set fso = CreateObject("FileSystemObject")
fso.CreateFolder folderPathWithName
Else
MsgBox "Folder already exists"
Exit Sub
End If
End Sub