Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as Msg box | Excel Discussion (Misc queries) | |||
cannot edit and save | New Users to Excel | |||
cannot edit and save | Excel Discussion (Misc queries) | |||
Save & Save As features in file menu of Excel | Excel Discussion (Misc queries) | |||
Missing "Save" and "Save As" functions | Excel Discussion (Misc queries) |