Creating folder with cell as name

Question

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

FileSystemObject for creating folders? stackoverflow.com/questions/31033820/… or MkDir — QHarr
– QHarr, Commented Apr 4, 2018 at 9:37
unrelatred observation: you know that ActiveWorkbook.Saved = True ActiveWorkbook.Close closes the current workbook without saving it, right? — ashleedawg
– ashleedawg, Commented Apr 4, 2018 at 9:52

QHarr · Accepted Answer · 2018-04-04 10:12:00Z

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

ashleedawg · Accepted Answer · 2018-04-04 09:43:46Z

0

Use MkDir create a folder using VBA:

MkDir "FolderName"

...creates a folder named "FolderName" in the current directory, or:

MkDir "c:\users\bob\desktop\FolderName"

...creates a folder named "FolderName" on Bob's desktop.

To create folder W:\My system\me\my work\PROJECTS\Projects\Reliability, use:

MkDir "W:\My system\me\my work\PROJECTS\Projects\Reliability"

More info here (but there's not much more to say).

answered Apr 4, 2018 at 9:43

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

2 Comments

Darren Hadland Over a year ago

So by adding this after the line of code .Cells(MyRow, "Q").Value = Sheets("Tracker").Cells(3, "E").Value, this will add a folder in the dir with the cell value in active column 17?

ashleedawg Over a year ago

does Sheets("Tracker").Cells(3, "E").Value contain the folder name? (A value similar to W:\My system\me\my work\PROJECTS\Projects\Reliability?) If so, then yes, but change the code to MkDir Sheets("Tracker").Cells(3, "E").Value.

Collectives™ on Stack Overflow

Creating folder with cell as name

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related