Save as Msg box

[monster]

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.

[monster]

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]

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

[monster]

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
>