microsoft.public.excel.programming

Martin Fishlock

12/20/2006 4:54:00 AM

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/D...

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Riggi" wrote:

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