#1   Report Post  
monster
 
Posts: n/a
Default 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.


  #2   Report Post  
monster
 
Posts: n/a
Default

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.


  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #4   Report Post  
monster
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save as Msg box Bob Umlas, Excel MVP Excel Discussion (Misc queries) 0 August 29th 05 09:56 PM
cannot edit and save jp New Users to Excel 1 February 9th 05 04:25 AM
cannot edit and save jp Excel Discussion (Misc queries) 0 February 8th 05 04:27 AM
Save & Save As features in file menu of Excel Blue Excel Discussion (Misc queries) 9 December 27th 04 09:49 PM
Missing "Save" and "Save As" functions EMSchon Excel Discussion (Misc queries) 2 December 27th 04 09:01 PM


All times are GMT +1. The time now is 07:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"