User selected save path

[JimM]

Hi

I'm trying to write some code that will split out worksheets from my workbook and save them to a user selected directory. The directory may or may not exist at the time of running the code.
How can I get a pop window that will allow the user to navigate to\create the save directory but will not require the 'save name' as I want the macro to assign the name for the workbook

Thanks

Jim

 

[Yongle]

try something like this

Sub GetPath()
    Dim fName As String, fPath As String, newFolder, newPath As String, fullPath As String
    fName = "FileNameDeterminedByVBA.xlsm"
    fPath = GetFolder & "\"
    newFolder = InputBox("Enter name of new subfolder" & vbCr & "Leave blank to save to chosen folder" & vbCr & fPath)
    If Len(newFolder) > 0 Then
        newPath = fPath & newFolder
        On Error Resume Next
        MkDir (newPath)
        If Err.Number <> 0 Then
            MsgBox "Unable to create folder"
            GoTo Handling
        End If
        fPath = newPath & "\"
    End If
    
    fullPath = fPath & fName
    MsgBox fullPath

Handling: On Error GoTo 0

End Sub

Private Function GetFolder() As String
    Dim s As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo CarryOn
        s = .SelectedItems(1)
    End With
CarryOn:
    GetFolder = s
End Function

 

[JimM]

Thanks Yongle

Was just coming back to post that I'd found a solution but appreciate your efforts anyway

In case anyone is interested this is the code I went with

'Select output folder where output files will be saved
Set SaveDialogBox = Application.FileDialog(msoFileDialogFolderPicker)
MsgBox ("Select a TARGET folder where opened file will be saved")
If SaveDialogBox.Show = -1 Then
OutputFolder = SaveDialogBox.SelectedItems(1)
End If

 

[Yongle]

glad you have a solution