MkDir VBA

[theman4392000]

I'm having a problem creating an excel vba code that creates a folder on the desktop and saves it under a name that I have on the excel sheet.

I have the code configured to work for my computer, but it won't work on others because the code reads:

Sheets("Folder Creator").Select

fnamepdf = Range("B8").Value

MkDir "C:\Users\Smith\Desktop\Roof Projects\" & fnamepdf

fnamepdf2 = "C:\Users\Smith\Desktop\Roof Projects\" & fnamepdf & "\" & fnamepdf & " Job Folder.pdf"

Not everyone is a Smith User. How can I change that so anyone can use the code and have it create a folder on their desktop, on their computer?

I have windows 7, but others may be using windows vista

 

[John_w]

Try this function to get the user's Desktop folder:

Private Function Get_SpecialFolderPath(strSpecialFolder) As String

    Dim WSshell As Object
     
    Set WSshell = CreateObject("WScript.Shell")
    Get_SpecialFolderPath = WSshell.SpecialFolders(strSpecialFolder)
    Set WSshell = Nothing
    
End Function

Call it like this:

Sub test()
    Dim desktopFolder As String
    desktopFolder = Get_SpecialFolderPath("Desktop")
End Sub

 

[theman4392000]

I'm fairly new to VBA. Is there any way you can explain what that does and how to input it into the code? Would I put the private sub into the ThisWorkbook file? How could that connect with what I already have?

Thanks so much for your quick response.

 

[John_w]

WSshell.SpecialFolders returns the folder path of the specified 'special' Windows folder - in this case it is called with the argument "Desktop", so it returns the folder path of the user's Desktop folder. See http://msdn.microsoft.com/en-us/library/0ea7b5xe(VS.85).aspx.

Incorporate it into your code like this (all code goes in the ThisWorkbook module if you want it executed when the workbook is opened):

Option Explicit

Private Sub Workbook_Open()

    Dim fnamepdf As String, fnamepdf2 As String
    Dim desktopFolder As String
    
    Sheets("Sheet1").Select
    fnamepdf = Range("B8").Value
    
    desktopFolder = Get_SpecialFolderPath("Desktop")
    
    MkDir desktopFolder & "\Roof Projects\" & fnamepdf
    
    fnamepdf2 = desktopFolder & "\Roof Projects\" & fnamepdf & "\" & fnamepdf & " Job Folder.pdf"
    
    MsgBox fnamepdf2

End Sub


Private Function Get_SpecialFolderPath(strSpecialFolder) As String

    Dim WSshell As Object
     
    Set WSshell = CreateObject("WScript.Shell")
    Get_SpecialFolderPath = WSshell.SpecialFolders(strSpecialFolder)
    Set WSshell = Nothing
    
End Function