VBA: Saving file [Changing filePath= to be a user-prompted location]

[lummers]

Hello!

I've been able to cobble the code below, using bits and pieces I have found online.

The below works fine and does what I need it to do. However, the file is generated in a static location but I want (a) the user to select where to save the file, (b) the saved file to retain the specified format in the code, in this case, KML.

Is the easiest solution simply to ask the user which folder to store the file and let the code do the rest?

I have tried a couple of different solutions but getting tripped up around this:

Open filePath For Output As FF

Any help would be really welcome! Thank you.

Private Sub Woof_Click()

Dim wsData As Worksheet
Dim rng As Range
Dim strName As String
Dim FF As Long

    
    strCRLF = StrConv(vbCrLf, vbUnicode)
    
    Set wsData = Sheets("Final KML")
    
    filePath = "C:\Test\data.kml"
        
    FF = FreeFile
    
    Open filePath For Output As FF
              
    Set rng = wsData.Range("B1")
      
    Do
        strName = StrConv((rng.Value), vbUnicode)
                        
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , strName;
        
        Set rng = rng.Offset(1)
    
    Loop Until rng.Value = "FINISH HIM!"
        
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 

End Sub

 

[Yongle]

I want (a) the user to select where to save the file, (b) the saved file to retain the specified format in the code, in this case, KML.

Replace this line:

filepath = "C:\Test\data.kml"

with these 7 lines:

GetPath:
    filepath = GetFolder
    If Len(filepath) = 0 Then
        GoTo GetPath
    Else
        filepath = filepath & "\data.kml"
    End If

and add this function below your procedure:

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

 

[Yongle]

Is the file name static

If not, use something like this...

 [COLOR=#b22222]   Dim fName As String
    fName [/COLOR]= InputBox("Name to save file?", , "Data") & ".kml"
GetPath:
    filepath = GetFolder
    If Len(filepath) = 0 Then
        GoTo GetPath
    Else
        filepath = filepath &[COLOR=#b22222] "\" & fName[/COLOR]
    End If