Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName to
say "Open" for the title, to list a default file name in the file line, and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell data
to a text file in the specificed location and with the specificed file name.
Thanks
TedT

Take a look at the GetSaveAsFileName method.

In article ,
TedT wrote:

Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName to
say "Open" for the title, to list a default file name in the file line, and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell data
to a text file in the specificed location and with the specificed file name.
Thanks
TedT

TedT,

Not sure I understand, but did you look at GetSaveAsFileName?

hth,

Doug

"TedT" wrote in message
...
Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I
have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName
to
say "Open" for the title, to list a default file name in the file line,
and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell
data
to a text file in the specificed location and with the specificed file
name.
Thanks
TedT

The code below should help. Use GetSaveAsFilename instead of GetOpenFileName.

Sub savefile()

fileToOpen = Application _
.GetSaveAsFilename("test.txt", _
filefilter:="Text Files (*.txt), *.txt")
ActiveSheet.CommandButton1.Caption = "Open"
ActiveSheet.CommandButton1.Caption = "Close"
End Sub

"TedT" wrote:

Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName to
say "Open" for the title, to list a default file name in the file line, and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell data
to a text file in the specificed location and with the specificed file name.
Thanks
TedT

All the suggestions have got it working. But one issue, if I select a file
that already exists, how do I warn about the user writing over the file.... I
thought there was some error message?

Or is there a command to check if the file exisits, and then if it exisits I
can prompt the user to continue?

Thanks
Ted

"Joel" wrote:

The code below should help. Use GetSaveAsFilename instead of GetOpenFileName.

Sub savefile()

fileToOpen = Application _
.GetSaveAsFilename("test.txt", _
filefilter:="Text Files (*.txt), *.txt")
ActiveSheet.CommandButton1.Caption = "Open"
ActiveSheet.CommandButton1.Caption = "Close"
End Sub

"TedT" wrote:

Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName to
say "Open" for the title, to list a default file name in the file line, and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell data
to a text file in the specificed location and with the specificed file name.
Thanks
TedT

The GetSaveAsFilename doesn't open the file it just gets a name. the code
below is more complicate but does a check of the files name before it open
the file.

Sub savefile()

Const ForWriting = 2

Set Newtextfile = CreateObject("Scripting.FileSystemObject")
filetoopen = Application _
.GetSaveAsFilename("test.txt", _
filefilter:="Text Files (*.txt), *.txt")

foundname = Dir(filetoopen)
If Len(foundname) = 0 Then

Newtextfile.CreateTextFile filetoopen
Set Newtextfile = Newtextfile.getfile(filetoopen)
Set FSNewtextfile = Newtextfile. _
OpenAsTextStream(ForWriting)
FSNewtextfile.Close

Else

MsgBox ("warning, file exists")
End If

ActiveSheet.CommandButton1.Caption = "Open"
ActiveSheet.CommandButton1.Caption = "Close"
End Sub


"TedT" wrote:

All the suggestions have got it working. But one issue, if I select a file
that already exists, how do I warn about the user writing over the file.... I
thought there was some error message?

Or is there a command to check if the file exisits, and then if it exisits I
can prompt the user to continue?

Thanks
Ted

"Joel" wrote:

The code below should help. Use GetSaveAsFilename instead of GetOpenFileName.

Sub savefile()

fileToOpen = Application _
.GetSaveAsFilename("test.txt", _
filefilter:="Text Files (*.txt), *.txt")
ActiveSheet.CommandButton1.Caption = "Open"
ActiveSheet.CommandButton1.Caption = "Close"
End Sub

"TedT" wrote:

Hi,
Everyone has been a big help in my Excel programming efforts. I have one
more question.

I am saving the data from the cells in a spreadsheet to a text file. I have
been able to use GetOpenFileName to find the file and then I can read it a
line at a time.

Now I need to save new data. Is there a way to get the GetOpenFileName to
say "Open" for the title, to list a default file name in the file line, and
change the "open" button to a "save" button? Once I have the location and
file name the user wants, I will use another routine to write the cell data
to a text file in the specificed location and with the specificed file name.
Thanks
TedT

There's another example of the same thing, at this post...

Subject: GetSaveAsFilename alwyas overwrites existing file
3/11/2007 3:21 PM PST
By: jeff
In: microsoft.public.excel.programming


Basically, you need to look at the Length of the value returned by the Dir()
command:

If Len(Dir(FileNameChoosen) ) <0 then
'File already exsits, so put code here to find out what user wants to do!
Else
'File does not already exist, so put code here to continue with save.
End If