VBA to create folders

[Glenno]

hi, have a perplexing problem. i run an engineering company. we have a spreadsheet with multiple columns for customer number (4 digits), job number (4 digits), plus description of the work, $ value etc. Each new job enters the data for the new job on the next row in the spreadsheet.
What i am looking to do is create a Macro that upon completion of all data input, i either hit a macro button, or type yes, or something similar, and it will automatically copy and create the existing project file structure with all master files with the new customer, job no and description as the file name in the file tree under "Projects", ie:

1234.5678 Project Description
|_ Quotations
|_Master quote file.xls, etc

Any ideas please?

 

[Hui]

Glenno

Firstly, Welcome to the Chandoo.org Forums

You can use the MkDir function

Dim myFolder as String
myFolder = "1234.5678 Project Description"
MkDir myFolder
MkDir myFolder & "\Quotations"
MkDir myFolder & "\Master quote"

 

[Glenno]

Thanks for your prompt response Hui. I knew when writing it was going to be difficult to explain what i was trying to do. i'm aware of the mkDir function, and its sort of right but not quite.

i have the new job as shown at Line 41:-


Line 41 will be populated, and once complete, i want to be able to hit the button at Column x that will then create the folder structure, as follows, but copied from a master folder structure.



The folder structure needs to be placed under the customer no, and if it doesn't exist, create it. The master folder is set up as follows, and includes various files needed to manage a project.

 

[Chihiro]

You can just use Dir function to check if folder exists. If not, use mkdir to create one.

If Len(Dir("C:\Some folder\path", vbDirectory)) = 0 Then
    MkDir "C:\Some folder\path"
End If

 

[Glenno]

Thanks Chihiro but that only does the same thing, ie: create a folder. i want to copy an existing master project folder that contains proformas for use during a project

 

[Chihiro]

Then just adopt one of Ron’s code to your need.
https://www.rondebruin.nl/win/s3/win026.htm

 

[Glenno]

excellent thank you

 

[Glenno]

i now have a further issue. i have solved creation of the folders, but now want to automatically name and place the folder. i want to to look in the Projects directory to see if there is a folder existing based on the last line of text, (in this case 5278, Line 41), then search for Customer Folder 5278, and create a subfolder using the job no, eg: 52780002. The difficulty is automatically using the last line of data each time, rather than do it manually.

Option Explicit

Function GetFolderPath() As String
    Dim oShell As Object
    Set oShell = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please select folder", 0, "C:\Users\Design Laptop1\Dropbox (RMR Engineering)\RMR Engineering - General\RMR - Projects\")
    If Not oShell Is Nothing Then
        GetFolderPath = oShell.Items.Item.Path
    Else
        GetFolderPath = vbNullString
    End If
    Set oShell = Nothing
End Function

Sub Testxl()
    Dim FName As String
    Dim WbName As String
    Dim Search As String
    Dim Prompt As String
    Dim Title As String
    Dim MyDir1 As String
    Dim MyDir2 As String
    Dim Passed As Long
    
    MyDir1 = "\Project Files"
        
    
On Error GoTo Err:
    
    FName = GetFolderPath
    If FName <> vbNullString Then
        Prompt = "Please Input a Directory Name (Customer no. and Name)"
        Title = "Name"
        Search = InputBox(Prompt, Title)
        If Search = "" Then Exit Sub
    End If
    FName = FName & "\" & Search
    MkDir FName
    ActiveWorkbook.SaveAs FName & "\" & Search & ".xls"
    
    MkDir ActiveWorkbook.Path & MyDir1
        
     'Test for existence of new folders.files
    Passed = 1
    GetAttr (FName)
    Passed = 2
    GetAttr (FName & "\" & Search & ".xls")
    Passed = 3
    GetAttr (ActiveWorkbook.Path & MyDir1)
  
    
    
    End
    'Sheets("Sheet1").Range("B1").Value = Search **Add if you require the name to be recorded in your spreadsheet
Err:
Select Case Err
Case 53:           MsgBox "File/Folder not created. Failed at step " & Passed
Case 75:    MsgBox "Folder already exists"
End Select
End Sub

 

[Chihiro]

Upload sample workbook with desensitized data. While keeping data structure and data type (string, numeric etc) same as your actual workbook.

It's so much easier to help you with sample file.

 

[Glenno]

Thanks Chihiro, file attached. Currently, the user starts to type the custoemr number in column D which then autofills. the customer number in column A will autofill column D. The user then filters Column C to see waht the last number was for that customer, type the next job number in Column B, and then Column C is auto populated with the 8 digit job number.
You will see that there is a Create New Folder macro button, that takes the user to the directory, but then they have to manually type the 8 digit job number and description, per:



The second macro button is to then place the generic file/folder structure under that newly created job number automatically as well, but that is for a later time. i need to resolve this job number creation first

Thanks in advance