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

lummers

New Member
Joined
Dec 10, 2017
Messages
9
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:
Code:
Open filePath For Output As FF

Any help would be really welcome! Thank you.


Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:
Code:
filepath = "C:\Test\data.kml"
with these 7 lines:
Code:
GetPath:
    filepath = GetFolder
    If Len(filepath) = 0 Then
        GoTo GetPath
    Else
        filepath = filepath & "\data.kml"
    End If
and add this function below your procedure:
Code:
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
 
Last edited:
Upvote 0
Is the file name static

If not, use something like this...

Code:
 [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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top