create a directory then save the workbook into it

[gibbo1715]

I was wondering if it was possible to create a folder first and then save the workbook into that folder so I had a play and came up with the code below

Having received a lot of help lately from this forum ( Especially Jake cheers DrJ) I thought it only fair I return the favour

There is no real error handling here and the code may need tidying up but it does work as follows, hope it proves useful to you all

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String
    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long
    With BrowseInfo
        .hOwner = 0
        .pidlRoot = 0
        .pszDisplayName = String$(MAX_PATH, vbNullChar)
        .lpszINSTRUCTIONS = Caption
        .ulFlags = BIF_RETURNONLYFSDIRS
        .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
        Res = SHGetPathFromIDListA(ID, FolderName)
        If Res Then
            BrowseFolder = Left$(FolderName, InStr(FolderName, _
                vbNullChar) - 1)
        End If
        End If
End Function
 
Sub Test()
    Dim FName As String
    Dim WbName As String
    Dim Search As String
    Dim Prompt As String
    Dim Title As String
On Error GoTo Err:
FName = BrowseFolder("Select A Folder")
    If FName = "" Then
        MsgBox "You didn't select a folder"
    Else
    FName = FName & "\TestDirectory"
    MkDir FName
    MsgBox FName & " created"
    End If
Prompt = "What do you want to search for?"
    Title = "Search Criteria"
    Search = InputBox(Prompt, Title)
ActiveWorkbook.SaveAs FName & "\" & Search & ".xls"
End
Err:
MsgBox " There has been an error, Please ensure the directory does not already exist"
End Sub

Cheers

[mvidas]

Hi Gibbo,

Just a couple quick additions, you may want to check to see if Search is blank before saving, like

Prompt = "What do you want to search for?"
Title = "Search Criteria"
Search = InputBox(Prompt, Title)
If Search = "" Then Exit Sub

Also, if you want the ability to create a new directory in the same pop-up window (and you have windows 2000 or greater), then add the following line to your declarations at the top:

Private Const BIF_NEWDIALOGSTYLE     As Long = &H64

and change the following lines in your BrowseFolder function

'.ulFlags = BIF_RETURNONLYFSDIR
.ulFlags = BIF_RETURNONLYFSDIR Or BIF_NEWDIALOGSTYLE

You may want to add this as a KB entry! Select KBase from the top menu, then create an entry!
Matt

[gibbo1715]

Thanks Matt

I ve added the ability to create a couple of sub folders as well as that was something I needed and may be useful and have done a KB Entry for the code as below

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
'if you want the ability to create a new directory in the same pop-up window
'(and you have windows 2000 or greater), then add the following line
'Private Const BIF_NEWDIALOGSTYLE     As Long = &H64
Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String
    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long
    With BrowseInfo
        .hOwner = 0
        .pidlRoot = 0
        .pszDisplayName = String$(MAX_PATH, vbNullChar)
        .lpszINSTRUCTIONS = Caption
        .ulFlags = BIF_RETURNONLYFSDIRS
'If win 2000 or above
'.ulFlags = BIF_RETURNONLYFSDIR Or BIF_NEWDIALOGSTYLE
        .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
        Res = SHGetPathFromIDListA(ID, FolderName)
        If Res Then
            BrowseFolder = Left$(FolderName, InStr(FolderName, _
                vbNullChar) - 1)
        End If
        End If
End Function
 
Sub Test()
    Dim FName As String
    Dim WbName As String
    Dim Search As String
    Dim Prompt As String
    Dim Title As String
On Error GoTo Err:
FName = BrowseFolder("Select A Folder")
    If FName = "" Then
        MsgBox "You didn't select a folder"
    Else
    Prompt = "Please Select a name for your enquiry?"
    Title = "Enquiry Name"
    Search = InputBox(Prompt, Title)
    If Search = "" Then Exit Sub
    End If
    FName = FName & "\" & Search
    MkDir FName
    MsgBox FName & " created"
    End If
    Sheets("Hidden").Range("B1").Value = Search
    ActiveWorkbook.SaveAs FName & "\" & Search & ".xls"
MkDir ActiveWorkbook.Path & "\TestDir1"
    MkDir ActiveWorkbook.Path & "\TestDir2"
End
Err:
MsgBox " There has been an error, Please ensure the directory does not already exist"
End Sub