Saving txt file from excel by specifying path - drop down directory explorer

[Riggi]

Hi,
I am trying a save a txt file generated by excel to a folder in C
drive. I know how to save the file by writing the path. But, I was
wondering is it possible to see a drop down menu to select the
location(directory) and then giving the filename.

presently I am using this code to save a txt file....

Dim filename As Object
Set filename = Sheet1.Range("G79")

Open filename For Output As #1


I want to add a dropdown menu for the user to select the directory and
drive he wants to save his file in.

Please Help!!1

Thanks,

Rumeet

 

[Guest]

Rummet,

If you want to open a dialog to save the file (ie get the save file name)
then try this:

Function GetSaveAsTxtFilename(Optional InitialFileName As Variant) As String
Dim vFilename As Variant
If IsMissing(InitialFileName) Then
InitialFileName = ""
End If
vFilename = _
Application.GetSaveAsFilename( _
InitialFileName:=InitialFileName, _
Title:="Please select a folder and name to save the file", _
filefilter:="Text files *.txt (*.txt),")
If vFilename = False Then
'cancel pressed
GetSaveAsTxtFilename = ""
Else
GetSaveAsTxtFilename = vFilename
End If
End Function

otherwise you will need to use a directory selection tool and take a look at
Jim Rech's code on Stephen Bullen's site
http://www.bmsltd.co.uk/MVP/Default.htm

 

[Riggi]

Thanks, it worked!!

Rummet,

If you want to open a dialog to save the file (ie get the save file name)
then try this:

Function GetSaveAsTxtFilename(Optional InitialFileName As Variant) As String
Dim vFilename As Variant
If IsMissing(InitialFileName) Then
InitialFileName = ""
End If
vFilename = _
Application.GetSaveAsFilename( _
InitialFileName:=InitialFileName, _
Title:="Please select a folder and name to save the file", _
filefilter:="Text files *.txt (*.txt),")
If vFilename = False Then
'cancel pressed
GetSaveAsTxtFilename = ""
Else
GetSaveAsTxtFilename = vFilename
End If
End Function

otherwise you will need to use a directory selection tool and take a look at
Jim Rech's code on Stephen Bullen's site
http://www.bmsltd.co.uk/MVP/Default.htm

 

[Riggi]

How do I add another function to it which would ask the user to save it
in another name/replace the file if he is tryin to save a file which
already exists.

Please Help!!!!


Thanks,

Riggi