Create Folder Using VBA

[CainyUK]

Hi all,

Hoping someone can help me with this "Run-time error '76': Path not found" problem that im getting

I have a sharepoint directly mapped to my C drive, and im using this code to try and create a folder in said directory but when running im getting an error

my foldername required is in the string strDirname which is housed in the sheet in cell a1

Sub CreateSharePointDirectory()
    Dim folderPath As String
    Dim ans As String
    Dim userName As String
    Dim strDirname As String
    
strDirname = Sheets("Customer Profile").Range("a1")
    
    userName = Replace(Application.userName, " ", ".")

    
    folderPath = "C:\Users\" & userName & "\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2"
    If Dir(folderPath, vbDirectory) = vbNullString Then
        ans = MsgBox("Folder does not exist. Create?", vbYesNo)
        If ans = vbYes Then
            MkDir folderPath & strDirname
        Else
            Exit Sub
        End If
    End If
End Sub

i dont know if the error is due to the way its grabbing the username however my username on windows is just my first name , all lower case no spaces

my error is coming up at this part MkDir folderPath & strDirname

im sure its something simple, but i cant figure it out

thanks in advance for any help offered

 

[HighAndWilder]

Hi all,

Hoping someone can help me with this "Run-time error '76': Path not found" problem that im getting

I have a sharepoint directly mapped to my C drive, and im using this code to try and create a folder in said directory but when running im getting an error

my foldername required is in the string strDirname which is housed in the sheet in cell a1

Sub CreateSharePointDirectory()
    Dim folderPath As String
    Dim ans As String
    Dim userName As String
    Dim strDirname As String
   
strDirname = Sheets("Customer Profile").Range("a1")
   
    userName = Replace(Application.userName, " ", ".")

   
    folderPath = "C:\Users\" & userName & "\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2"
    If Dir(folderPath, vbDirectory) = vbNullString Then
        ans = MsgBox("Folder does not exist. Create?", vbYesNo)
        If ans = vbYes Then
            MkDir folderPath & strDirname
        Else
            Exit Sub
        End If
    End If
End Sub

i dont know if the error is due to the way its grabbing the username however my username on windows is just my first name , all lower case no spaces

my error is coming up at this part MkDir folderPath & strDirname

im sure its something simple, but i cant figure it out

thanks in advance for any help offered

Does the folder "C:\Users\" & userName & "\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2" actually exist?

What is the value in cell a1?

Try a "\" at the end of folderPath.

 

[CainyUK]

Does the folder "C:\Users\" & userName & "\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2" actually exist?

What is the value in cell a1?

Try a "\" at the end of folderPath.

Hi ,

The folders do exist - ive copied the actual file path and replaced the company sensitive info with dummy info above

Cell a1 is just text for the name of the folder i want creating

Tried a "\" at the end of folder path too and still getting the same errorr :/

 

[CainyUK]

found the issue - it was using my microsoft account username in the filepath and not my computer username - Solved

 

[HighAndWilder]

found the issue - it was using my microsoft account username in the filepath and not my computer username - Solved

Good. Something no one else would have known about.

 

[smozgur]

found the issue - it was using my microsoft account username in the filepath and not my computer username - Solved

Application.userName normally returns the current user name in Windows. Does it return your Microsoft account username?
Are you using user name as text now, i.e. "C:\Users\smozgur\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2"?

 

[CainyUK]

Application.userName normally returns the current user name in Windows. Does it return your Microsoft account username?
Are you using user name as text now, i.e. "C:\Users\smozgur\CompanyName\CompanyDocsFolder\Subfolder1\SubFolder2"?

Yeah so it was pulling through my office365 username so ‘firstname.surname’ . But the file path needed to be my local pc username which is ‘firstname’ . So i used the environment function to pull the pc username and that worked

 

[smozgur]

Yeah so it was pulling through my office365 username so ‘firstname.surname’ . But the file path needed to be my local pc username which is ‘firstname’ . So i used the environment function to pull the pc username and that worked

Interesting. It is not working like that in my Windows computer. Perhaps something related to SharePoint. That's why I wanted to make sure. Thanks for clarifying.

In the meantime, "Solved" is not an actual solution to be marked, so I removed the solution mark from your post. Can you please send the code that you used to get the environment variable instead of userName = Replace(Application.userName, " ", ".") line, then you can mark that post as the solution to help the future readers?

 

[CainyUK]

Interesting. It is not working like that in my Windows computer. Perhaps something related to SharePoint. That's why I wanted to make sure. Thanks for clarifying.

In the meantime, "Solved" is not an actual solution to be marked, so I removed the solution mark from your post. Can you please send the code that you used to get the environment variable instead of userName = Replace(Application.userName, " ", ".") line, then you can mark that post as the solution to help the future readers?

Will do when i get home on the computer