Making Directories Dynamically

[sassriverrat]

Hello All-

I've asked this question before a number of different ways but I've never gotten an answer that worked.

I have a workbook that's name when it saved comes from a series of cells.
The file name when it's saved is formatted as follows and we'll just say it's from Sheet1
A1 A2 A3 - A4
A1 is a number
A2 is a letter
A3 is a name
A4 is a name

The file gets saved at C:\Users\Bridge\Desktop\Noons\Noon-Arrivals\ 'the Folder I'm trying to generate

So here is where I'm not sure how the code should be written. The path above is defined in,
let's say, cell B2 from Sheet1
Where there is the 'The folder I'm trying to generate piece above, the files are archived based on the number in A1, and in increments of 50 (currently). So currently I have 377 files (and building). If the number in A1 is 40, for example, then that file is saved in the first folder (1-50). If the value of A1 is 352, then it's saved in the folder named (351-400), but I'm having to manually create these folders to and then save these. I was wondering if someone knew how to write a code where it could make the folder directory based on the increment value (let's say a number set in B3 of sheet1). Hopefully this makes sense.

Thanks!

 

[Yongle]

Is this what you want?
- test with different values in A1

Sub BuildFolderPath()
    Dim path As String:     path = "C:\Users\Bridge\Desktop\Noons\Noon-Arrivals\"
    Dim x As Integer:       x = Int((Range("A1") - 1) / 50) * 50
    Dim fldr As String:     fldr = 1 + x & "-" & 50 + x
    MsgBox "File: " & Range("A1") & vbTab & "Folder: " & fldr & vbCr & vbCr & path & fldr
End Sub

 

[Akuini]

Hello All-

So here is where I'm not sure how the code should be written. The path above is defined in,
let's say, cell B2 from Sheet1
Where there is the 'The folder I'm trying to generate piece above, the files are archived based on the number in A1, and in increments of 50 (currently). So currently I have 377 files (and building). If the number in A1 is 40, for example, then that file is saved in the first folder (1-50). If the value of A1 is 352, then it's saved in the folder named (351-400), but I'm having to manually create these folders to and then save these. I was wondering if someone knew how to write a code where it could make the folder directory based on the increment value (let's say a number set in B3 of sheet1). Hopefully this makes sense.

Thanks!

Here's another way:

Sub a1078693a()
Dim path1 As String, path2 As String, n As Long
    path1 = "C:\Users\Bridge\Desktop\Noons\Noon-Arrivals\" 'sheets("Sheet1").Range("B2") 'must have "\" at the end
    n = 50  'sheets("Sheet1").Range("B3")
With CreateObject("Scripting.FileSystemObject")
    For i = 1 To 201 Step n
        path2 = path1 & i & "-" & (i + n - 1)
        If Not .FolderExists(path2) Then .CreateFolder path2
    Next
End With
End Sub

 

[Yongle]

Or as a formula

folder
=1+INT((A1 - 1) / 50) * 50&"-"&50+INT((A1 - 1) / 50) * 50

full path
="C:\Users\Bridge\Desktop\Noons\Noon-Arrivals/"&1+INT((A1 - 1) / 50) * 50&"-"&50+INT((A1 - 1) / 50) * 50

amend the / to \
(forum refuses original formula)

 

[Yongle]

@Alkuini gave you another way to create the folder string

Here is my previous code with a another way to create the folder

Sub BuildFolderPath()
    Dim path As String:     path = "C:\Users\Bridge\Desktop\Noons\Noon-Arrivals\"
    Dim x As Integer:       x = Int((Range("A1") - 1) / 50) * 50
    Dim fldr As String:     fldr = 1 + x & "-" & 50 + x
   [COLOR=#008080] If Len(Dir(path & fldr, vbDirectory)) = 0 Then MkDir path & fldr[/COLOR]
End Sub

 

[sassriverrat]

So two things-
1. You both are FANTASTIC and far smarter than me. Thank you very very much.
2. I've modified Yongle's code as below (because it was easier for me to more quickly recognize, but still thank you both!) This was tweaked so that the increments (in my case 50) could be modified by the final user.

My one last question (I think) is this: Path("n22") is just the pathway typed out in a cell- exactly the same as you had in the macro except it's easily changed by the user.
I'd like this to create the subfolder (0-50) or (351-400) within this folder. Right now it was creating "Noons-Arrivals0-50" in the "Noons" folder. Does this make sense? The folders created should be just "#-#" within the specified path. Thanks again guys, seriously

Sub BuildFolderPath()
    Dim int1 As Integer:    int1 = Worksheets("Notes").Range("T23")
    Dim int2 As Integer:    int2 = Worksheets("Notes").Range("O26")
    Dim path As String:     path = Worksheets("Notes").Range("N22")
    Dim x As Integer:       x = Int((int2 - 1) / int1) * int1
    Dim fldr As String:     fldr = 1 + x & "-" & int1 + x
    MsgBox "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & vbCr & path & "\" & fldr
    If Len(Dir(path & fldr, vbDirectory)) = 0 Then MkDir path & fldr
End Sub

 

[Yongle]

Either add a backslash to path or insert it like this
(in the original code the backslash was at end of path string)

MkDir path & "\" & fldr

 

[sassriverrat]

so the code runs extremely well. It does, however, give me an error code 75 Path/File access error and it highlights the MkDir path & fldr Now this only happens after the file has been saved once. I'm guessing Excel is trying to write the same file here again?
see total modified code below
FYI- this code is linked to a "save" button on the last page of the workbook.

Thanks!

Private Sub SaveAsDirectory()'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet


    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim Path4 As String
    Dim Path5 As String
    Dim path6 As String
    Dim Path7 As String
    Dim Path8 As String
    Dim myfilename As String
    Dim fpathname As String
    Dim oldpathme As String
    Dim int1 As Integer:
    Dim int2 As Integer:
    Dim path As String:
    Dim x As Integer:
    Dim fldr As String:
    Path1 = Worksheets("Notes").Range("O26")
    Path2 = Worksheets("Notes").Range("P26")
    Path3 = Worksheets("Notes").Range("Q26")
    Path4 = Worksheets("Notes").Range("R26")
    Path5 = Worksheets("Notes").Range("S26")
    Path7 = Worksheets("Notes").Range("O17")
    Path8 = Worksheets("Notes").Range("O19")
    int1 = Worksheets("Notes").Range("T23")
    int2 = Worksheets("Notes").Range("O26")
    path = Worksheets("Notes").Range("N22")
    x = Int((int2 - 1) / int1) * int1
    fldr = 1 + x & "-" & int1 + x
    
    myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5
    fpathname = path & "\" & fldr & "\" & myfilename & ".xlsm"
    oldpathme = Path7 & "\" & Path8 & ".xlsm"
    
    ActiveSheet.EnableCalculation = False


   
    
    
    'To add a "Found it" box, add this piece of code in
        'If Dir("Type in the whole directory address here"
        'MkDir Directory Name
        'MsgBox "Done (or whatever else you want)"
        'Else
        'MsgBox "Found it"
        'End If
    'MsgBox "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & vbCr & path & "\" & fldr
    
    
    MsgBox "You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & _
    vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr
    If Len(Dir(path & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled


    'Call File Killer
    Kill (oldpathme)
    
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
    
End Sub

 

[Yongle]

Insert backslash inside the IF test - VBA needs to test for the same folder as MkDir

 

[sassriverrat]

Worked like a champ!
Thanks!