Search code examples
vbaexcelcreate-directory

Creating folder with cell as name


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

Solution

  • 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