Create follow up folder

[Arie Bos]

In a parent folder, I have a set of subfolders, each starting with a 5 digit follow up number(e.g. 23004), followed by a project name, e.g. "23004 - ACME Apples". In VBA, I want to read that number, and create a new subfolder with the next follow up number, and the project name "ACME Apples" to be read from cell C7 in sheet 'Customers'.

I have the following code, but get an error 76, 'Path not Found' on the line '"for each subFolder In CreateObject..."
But the 'Debug.Print " parentFolder = " & parentFolder' shows the correct folder.

'Create Project folder in \Sharepoint\Sales\ with company name as folder name
Public Function NewDir() As String
    NewDir = Environ("userprofile") & "\OneDrive \Sales\PO\"
Debug.Print " Newdir = " & NewDir
End Function
Sub btnCreateNextFollowUpFolder_Click()

    Dim parentFolder As String
    parentFolder = NewDir
    
Debug.Print " parentFolder = " & parentFolder
    
    Dim currentNumber As Integer
    currentNumber = 0
    
    'loop through subfolders to find the highest numbered follow up folder
    Dim subFolder As Variant
    For Each subFolder In CreateObject("Scripting.FileSystemObject").GetFolder(parentFolder).SubFolders
        Dim folderName As String
        folderName = subFolder.Name
        
        'extract the follow up number using a regular expression
        Dim regex As Object
        Set regex = CreateObject("VBScript.RegExp")
        regex.Pattern = "^\d{5}"
        If regex.Test(folderName) Then
            Dim folderNumber As Integer
            folderNumber = CInt(regex.Execute(folderName)(0))
            If folderNumber > currentNumber Then
                currentNumber = folderNumber
            End If
        End If
    Next
    
    'create the new follow up folder
    Dim newFolderNumber As Integer
    newFolderNumber = currentNumber + 1
    
    Dim projectName As String
    projectName = Worksheets("Customers").Range("C7").Value 'read project name from cell C7 in the Customers sheet
    
    Dim newFolderName As String
    newFolderName = Format(newFolderNumber, "00000") & " - " & projectName 'use project name in the folder name
    
    Dim newFolderPath As String
    newFolderPath = parentFolder & newFolderName
    
    CreateObject("Scripting.FileSystemObject").CreateFolder newFolderPath
End Sub

 

[PeteWright]

Try your code with a local path which does not lead to a cloud location like OneDrive, SharePoint, etc. since it's a well known issue (VBA + SharePoint)