Save as Msg box
 

I have the follwowing to save as a form:

Sub SaveAs()
'
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"
Do
fName = Application.GetSaveAsFilename
Loop Until fName < False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End Sub

Problems:
1. It doesn't go to the directory that I have above: "V:\Netshare\Item
Master Creation\2005 Item Request Submission"

2. I want to enter a message box that confirms that yes they want to save
after they have entered the name in the save as box. In the Excel hlp I was
just able to get this code:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If
....for this mesage box it only states the name of the file and an OK button
only, but when I hit the OK button it won't proceed, won't save. What I
would like is a message box with Yes & No button and if yes is selected to
save and if no to exit workbook and not save any changes.

Can you help? I really apprciate it.

Thank you bob, but I get a 'syntax error' when I try to run the macro. I'm
sorry am I doing anything wrong. This is the first time I do this. sorry.

"Bob Umlas, Excel MVP" wrote:

You first need to make sure the active drive is V or it won't change:
ChDrive "V"
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"

After your MsgBox you have no save command! Try:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
If(MsgBox "Save as " & fileSaveName,vbYesNo+VbQuestion)=vbNo then exit
sub
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End If
Bob Umlas
Excel MVP

"monster" wrote:

I have the follwowing to save as a form:

Sub SaveAs()
'
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"
Do
fName = Application.GetSaveAsFilename
Loop Until fName < False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End Sub

Problems:
1. It doesn't go to the directory that I have above: "V:\Netshare\Item
Master Creation\2005 Item Request Submission"

2. I want to enter a message box that confirms that yes they want to save
after they have entered the name in the save as box. In the Excel hlp I was
just able to get this code:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If
...for this mesage box it only states the name of the file and an OK button
only, but when I hit the OK button it won't proceed, won't save. What I
would like is a message box with Yes & No button and if yes is selected to
save and if no to exit workbook and not save any changes.

Can you help? I really apprciate it.

I think there were a couple of typos in the code:

Option Explicit
Sub testme()

Dim FileSaveName As Variant

ChDrive "V"
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"

FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If FileSaveName < False Then
If MsgBox("Save as " & FileSaveName, vbYesNo + vbQuestion) = vbNo Then
Exit Sub
end if
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlNormal
End If

End Sub

But xlNormal sure looks out of place with *.txt. Are you sure you mean that?

I like to record a macro to get the correct constants and then use them to build
the code.

Maybe you meant:
FileFormat:=xlText
or one of the other Text formats.



monster wrote:

Thank you bob, but I get a 'syntax error' when I try to run the macro. I'm
sorry am I doing anything wrong. This is the first time I do this. sorry.

"Bob Umlas, Excel MVP" wrote:

You first need to make sure the active drive is V or it won't change:
ChDrive "V"
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"

After your MsgBox you have no save command! Try:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
If(MsgBox "Save as " & fileSaveName,vbYesNo+VbQuestion)=vbNo then exit
sub
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End If
Bob Umlas
Excel MVP

"monster" wrote:

I have the follwowing to save as a form:

Sub SaveAs()
'
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"
Do
fName = Application.GetSaveAsFilename
Loop Until fName < False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End Sub

Problems:
1. It doesn't go to the directory that I have above: "V:\Netshare\Item
Master Creation\2005 Item Request Submission"

2. I want to enter a message box that confirms that yes they want to save
after they have entered the name in the save as box. In the Excel hlp I was
just able to get this code:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If
...for this mesage box it only states the name of the file and an OK button
only, but when I hit the OK button it won't proceed, won't save. What I
would like is a message box with Yes & No button and if yes is selected to
save and if no to exit workbook and not save any changes.

Can you help? I really apprciate it.



--

Dave Peterson

Thank you, it works like a charm.
I meant *.xls instead of *.txt

Thank you so much you guys are great.

"Dave Peterson" wrote:

I think there were a couple of typos in the code:

Option Explicit
Sub testme()

Dim FileSaveName As Variant

ChDrive "V"
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"

FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If FileSaveName < False Then
If MsgBox("Save as " & FileSaveName, vbYesNo + vbQuestion) = vbNo Then
Exit Sub
end if
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlNormal
End If

End Sub

But xlNormal sure looks out of place with *.txt. Are you sure you mean that?

I like to record a macro to get the correct constants and then use them to build
the code.

Maybe you meant:
FileFormat:=xlText
or one of the other Text formats.



monster wrote:

Thank you bob, but I get a 'syntax error' when I try to run the macro. I'm
sorry am I doing anything wrong. This is the first time I do this. sorry.

"Bob Umlas, Excel MVP" wrote:

You first need to make sure the active drive is V or it won't change:
ChDrive "V"
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"

After your MsgBox you have no save command! Try:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
If(MsgBox "Save as " & fileSaveName,vbYesNo+VbQuestion)=vbNo then exit
sub
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End If
Bob Umlas
Excel MVP

"monster" wrote:

I have the follwowing to save as a form:

Sub SaveAs()
'
ChDir "V:\Netshare\Item Master Creation\2005 Item Request Submission"
Do
fName = Application.GetSaveAsFilename
Loop Until fName < False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
End Sub

Problems:
1. It doesn't go to the directory that I have above: "V:\Netshare\Item
Master Creation\2005 Item Request Submission"

2. I want to enter a message box that confirms that yes they want to save
after they have entered the name in the save as box. In the Excel hlp I was
just able to get this code:
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If
...for this mesage box it only states the name of the file and an OK button
only, but when I hit the OK button it won't proceed, won't save. What I
would like is a message box with Yes & No button and if yes is selected to
save and if no to exit workbook and not save any changes.

Can you help? I really apprciate it.



--

Dave Peterson