macro to save as

Mo · 07-08-2005, 06:05 PM

I want the macro to give the user the option (yes or no) after the message of
"Save File As ...". The macro appear below. Any suggestions will be
greatly appreciated.

> Sub cmdExit_Click()
> Dim Response As String
> Dim msg As String
> Dim Style As String
> Dim sPath As String
> Dim sFilename As String
> Dim ans
>
> msg = "Are you sure you want to Exit the application and Close Excel?"
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> sPath = "C:\MetroWest\"
> sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> "mm-dd-yyyy")
> ans = MsgBox("Save File As " & sFilename)
> If ans = vbOK Then
> ActiveWorkbook.SaveAs sPath & sFilename
> ActiveWorkbook.Close savechanges:=True
> Application.Exit
> Application.StatusBar = "Application Closing."
> End If
> Else
> ActiveWorkbook.Activate
> End If
>
> End Sub

Scott Vincent · 07-09-2005, 01:05 AM

I am not exactly sure what you are trying to do, but here is my interpretation:

Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

sPath = "C:\MetroWest\"
sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
End If

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
ActiveWorkbook.Close savechanges:=True
Application.Exit
Application.StatusBar = "Application Closing."
Else
ActiveWorkbook.Activate
End If

Let me know if this is not exactly what you were trying to accomplish.
--
Happy Coding,

Scott

"Mo" wrote:

> I want the macro to give the user the option (yes or no) after the message of
> "Save File As ...". The macro appear below. Any suggestions will be
> greatly appreciated.
>
> > Sub cmdExit_Click()
> > Dim Response As String
> > Dim msg As String
> > Dim Style As String
> > Dim sPath As String
> > Dim sFilename As String
> > Dim ans
> >
> > msg = "Are you sure you want to Exit the application and Close Excel?"
> > Style = vbYesNo + vbInformation + vbDefaultButton2
> >
> > Response = MsgBox(msg, Style)
> > If Response = vbYes Then
> > sPath = "C:\MetroWest\"
> > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > "mm-dd-yyyy")
> > ans = MsgBox("Save File As " & sFilename)
> > If ans = vbOK Then
> > ActiveWorkbook.SaveAs sPath & sFilename
> > ActiveWorkbook.Close savechanges:=True
> > Application.Exit
> > Application.StatusBar = "Application Closing."
> > End If
> > Else
> > ActiveWorkbook.Activate
> > End If
> >
> > End Sub
>

Mo · 07-11-2005, 12:05 PM

Scott,

Sorry for the vague info, however I figure it out. But, I have two issues
with this code, I appreciate any suggestions. (1) I want to save the file
based on entries in two seperatae cells, the first being the cell containing
text and the second based on date (I have the date part already). (2)After
saving, I want the macro to completely exit Excel.

Below is the code:

Sub cmdExit_Click()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sPath = "C:\MetroWest\"
sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
"mm-dd-yyyy")

msg = "Save File As " & sFilename
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Exit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

"Scott Vincent" wrote:

> I am not exactly sure what you are trying to do, but here is my interpretation:
>
> Dim Response As String
> Dim msg As String
> Dim Style As String
> Dim sPath As String
> Dim sFilename As String
> Dim ans
>
> sPath = "C:\MetroWest\"
> sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
> ans = MsgBox("Save File As " & sFilename)
> If ans = vbOK Then
> ActiveWorkbook.SaveAs sPath & sFilename
> End If
>
> msg = "Are you sure you want to Exit the application and Close Excel?"
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> ActiveWorkbook.Close savechanges:=True
> Application.Exit
> Application.StatusBar = "Application Closing."
> Else
> ActiveWorkbook.Activate
> End If
>
> Let me know if this is not exactly what you were trying to accomplish.
> --
> Happy Coding,
>
> Scott
>
>
> "Mo" wrote:
>
> > I want the macro to give the user the option (yes or no) after the message of
> > "Save File As ...". The macro appear below. Any suggestions will be
> > greatly appreciated.
> >
> > > Sub cmdExit_Click()
> > > Dim Response As String
> > > Dim msg As String
> > > Dim Style As String
> > > Dim sPath As String
> > > Dim sFilename As String
> > > Dim ans
> > >
> > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > >
> > > Response = MsgBox(msg, Style)
> > > If Response = vbYes Then
> > > sPath = "C:\MetroWest\"
> > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > > "mm-dd-yyyy")
> > > ans = MsgBox("Save File As " & sFilename)
> > > If ans = vbOK Then
> > > ActiveWorkbook.SaveAs sPath & sFilename
> > > ActiveWorkbook.Close savechanges:=True
> > > Application.Exit
> > > Application.StatusBar = "Application Closing."
> > > End If
> > > Else
> > > ActiveWorkbook.Activate
> > > End If
> > >
> > > End Sub
> >

Scott Vincent · 07-11-2005, 02:05 PM

Is this what you are looking for?

sFilename = Worksheets("MainMenu").Range("C4").Value & " " &
Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")

Said Another Way:
sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value,
"mm-dd-yyyy")

Range("C4") is the text portion of the name. How do you validate that the
file name is valid?
--
Happy Coding,

Scott

"Mo" wrote:

> Scott,
>
> Sorry for the vague info, however I figure it out. But, I have two issues
> with this code, I appreciate any suggestions. (1) I want to save the file
> based on entries in two seperatae cells, the first being the cell containing
> text and the second based on date (I have the date part already). (2)After
> saving, I want the macro to completely exit Excel.
>
> Below is the code:
>
> Sub cmdExit_Click()
> Dim Response As String
> Dim msg As String
> Dim Style As String
> Dim sPath As String
> Dim sFilename As String
> Dim ans
>
> msg = "Are you sure you want to Exit the application and Close Excel?"
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> sPath = "C:\MetroWest\"
> sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> "mm-dd-yyyy")
>
> msg = "Save File As " & sFilename
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> ActiveWorkbook.SaveAs sPath & sFilename
> ActiveWorkbook.Close savechanges:=True
> Application.Exit
> Application.StatusBar = "Application Closing."
> End If
> Else
> ActiveWorkbook.Activate
> End If
>
> End Sub
>
> "Scott Vincent" wrote:
>
> > I am not exactly sure what you are trying to do, but here is my interpretation:
> >
> > Dim Response As String
> > Dim msg As String
> > Dim Style As String
> > Dim sPath As String
> > Dim sFilename As String
> > Dim ans
> >
> > sPath = "C:\MetroWest\"
> > sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
> > ans = MsgBox("Save File As " & sFilename)
> > If ans = vbOK Then
> > ActiveWorkbook.SaveAs sPath & sFilename
> > End If
> >
> > msg = "Are you sure you want to Exit the application and Close Excel?"
> > Style = vbYesNo + vbInformation + vbDefaultButton2
> >
> > Response = MsgBox(msg, Style)
> > If Response = vbYes Then
> > ActiveWorkbook.Close savechanges:=True
> > Application.Exit
> > Application.StatusBar = "Application Closing."
> > Else
> > ActiveWorkbook.Activate
> > End If
> >
> > Let me know if this is not exactly what you were trying to accomplish.
> > --
> > Happy Coding,
> >
> > Scott
> >
> >
> > "Mo" wrote:
> >
> > > I want the macro to give the user the option (yes or no) after the message of
> > > "Save File As ...". The macro appear below. Any suggestions will be
> > > greatly appreciated.
> > >
> > > > Sub cmdExit_Click()
> > > > Dim Response As String
> > > > Dim msg As String
> > > > Dim Style As String
> > > > Dim sPath As String
> > > > Dim sFilename As String
> > > > Dim ans
> > > >
> > > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > > >
> > > > Response = MsgBox(msg, Style)
> > > > If Response = vbYes Then
> > > > sPath = "C:\MetroWest\"
> > > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > > > "mm-dd-yyyy")
> > > > ans = MsgBox("Save File As " & sFilename)
> > > > If ans = vbOK Then
> > > > ActiveWorkbook.SaveAs sPath & sFilename
> > > > ActiveWorkbook.Close savechanges:=True
> > > > Application.Exit
> > > > Application.StatusBar = "Application Closing."
> > > > End If
> > > > Else
> > > > ActiveWorkbook.Activate
> > > > End If
> > > >
> > > > End Sub
> > >

Mo · 07-11-2005, 03:05 PM

Scott,

Thanks, it's working fine. "C4" is a locked cell. However, it does not
completely exits Excel.

Mo

"Scott Vincent" wrote:

> Is this what you are looking for?
>
> sFilename = Worksheets("MainMenu").Range("C4").Value & " " &
> Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
>
> Said Another Way:
> sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value,
> "mm-dd-yyyy")
>
> Range("C4") is the text portion of the name. How do you validate that the
> file name is valid?
> --
> Happy Coding,
>
> Scott
>
>
> "Mo" wrote:
>
> > Scott,
> >
> > Sorry for the vague info, however I figure it out. But, I have two issues
> > with this code, I appreciate any suggestions. (1) I want to save the file
> > based on entries in two seperatae cells, the first being the cell containing
> > text and the second based on date (I have the date part already). (2)After
> > saving, I want the macro to completely exit Excel.
> >
> > Below is the code:
> >
> > Sub cmdExit_Click()
> > Dim Response As String
> > Dim msg As String
> > Dim Style As String
> > Dim sPath As String
> > Dim sFilename As String
> > Dim ans
> >
> > msg = "Are you sure you want to Exit the application and Close Excel?"
> > Style = vbYesNo + vbInformation + vbDefaultButton2
> >
> > Response = MsgBox(msg, Style)
> > If Response = vbYes Then
> > sPath = "C:\MetroWest\"
> > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > "mm-dd-yyyy")
> >
> > msg = "Save File As " & sFilename
> > Style = vbYesNo + vbInformation + vbDefaultButton2
> >
> > Response = MsgBox(msg, Style)
> > If Response = vbYes Then
> > ActiveWorkbook.SaveAs sPath & sFilename
> > ActiveWorkbook.Close savechanges:=True
> > Application.Exit
> > Application.StatusBar = "Application Closing."
> > End If
> > Else
> > ActiveWorkbook.Activate
> > End If
> >
> > End Sub
> >
> > "Scott Vincent" wrote:
> >
> > > I am not exactly sure what you are trying to do, but here is my interpretation:
> > >
> > > Dim Response As String
> > > Dim msg As String
> > > Dim Style As String
> > > Dim sPath As String
> > > Dim sFilename As String
> > > Dim ans
> > >
> > > sPath = "C:\MetroWest\"
> > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
> > > ans = MsgBox("Save File As " & sFilename)
> > > If ans = vbOK Then
> > > ActiveWorkbook.SaveAs sPath & sFilename
> > > End If
> > >
> > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > >
> > > Response = MsgBox(msg, Style)
> > > If Response = vbYes Then
> > > ActiveWorkbook.Close savechanges:=True
> > > Application.Exit
> > > Application.StatusBar = "Application Closing."
> > > Else
> > > ActiveWorkbook.Activate
> > > End If
> > >
> > > Let me know if this is not exactly what you were trying to accomplish.
> > > --
> > > Happy Coding,
> > >
> > > Scott
> > >
> > >
> > > "Mo" wrote:
> > >
> > > > I want the macro to give the user the option (yes or no) after the message of
> > > > "Save File As ...". The macro appear below. Any suggestions will be
> > > > greatly appreciated.
> > > >
> > > > > Sub cmdExit_Click()
> > > > > Dim Response As String
> > > > > Dim msg As String
> > > > > Dim Style As String
> > > > > Dim sPath As String
> > > > > Dim sFilename As String
> > > > > Dim ans
> > > > >
> > > > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > > > >
> > > > > Response = MsgBox(msg, Style)
> > > > > If Response = vbYes Then
> > > > > sPath = "C:\MetroWest\"
> > > > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > > > > "mm-dd-yyyy")
> > > > > ans = MsgBox("Save File As " & sFilename)
> > > > > If ans = vbOK Then
> > > > > ActiveWorkbook.SaveAs sPath & sFilename
> > > > > ActiveWorkbook.Close savechanges:=True
> > > > > Application.Exit
> > > > > Application.StatusBar = "Application Closing."
> > > > > End If
> > > > > Else
> > > > > ActiveWorkbook.Activate
> > > > > End If
> > > > >
> > > > > End Sub
> > > >

Mo · 07-12-2005, 12:05 PM

"Mo" wrote:

> Scott,
>
> Thanks, it's working fine. "C4" is a locked cell. However, it does not
> completely exits Excel.
>
> Mo
>
> "Scott Vincent" wrote:
>
> > Is this what you are looking for?
> >
> > sFilename = Worksheets("MainMenu").Range("C4").Value & " " &
> > Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
> >
> > Said Another Way:
> > sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value,
> > "mm-dd-yyyy")
> >
> > Range("C4") is the text portion of the name. How do you validate that the
> > file name is valid?
> > --
> > Happy Coding,
> >
> > Scott
> >
> >
> > "Mo" wrote:
> >
> > > Scott,
> > >
> > > Sorry for the vague info, however I figure it out. But, I have two issues
> > > with this code, I appreciate any suggestions. (1) I want to save the file
> > > based on entries in two seperatae cells, the first being the cell containing
> > > text and the second based on date (I have the date part already). (2)After
> > > saving, I want the macro to completely exit Excel.
> > >
> > > Below is the code:
> > >
> > > Sub cmdExit_Click()
> > > Dim Response As String
> > > Dim msg As String
> > > Dim Style As String
> > > Dim sPath As String
> > > Dim sFilename As String
> > > Dim ans
> > >
> > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > >
> > > Response = MsgBox(msg, Style)
> > > If Response = vbYes Then
> > > sPath = "C:\MetroWest\"
> > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > > "mm-dd-yyyy")
> > >
> > > msg = "Save File As " & sFilename
> > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > >
> > > Response = MsgBox(msg, Style)
> > > If Response = vbYes Then
> > > ActiveWorkbook.SaveAs sPath & sFilename
> > > ActiveWorkbook.Close savechanges:=True
> > > Application.Exit
> > > Application.StatusBar = "Application Closing."
> > > End If
> > > Else
> > > ActiveWorkbook.Activate
> > > End If
> > >
> > > End Sub
> > >
> > > "Scott Vincent" wrote:
> > >
> > > > I am not exactly sure what you are trying to do, but here is my interpretation:
> > > >
> > > > Dim Response As String
> > > > Dim msg As String
> > > > Dim Style As String
> > > > Dim sPath As String
> > > > Dim sFilename As String
> > > > Dim ans
> > > >
> > > > sPath = "C:\MetroWest\"
> > > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy")
> > > > ans = MsgBox("Save File As " & sFilename)
> > > > If ans = vbOK Then
> > > > ActiveWorkbook.SaveAs sPath & sFilename
> > > > End If
> > > >
> > > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > > >
> > > > Response = MsgBox(msg, Style)
> > > > If Response = vbYes Then
> > > > ActiveWorkbook.Close savechanges:=True
> > > > Application.Exit
> > > > Application.StatusBar = "Application Closing."
> > > > Else
> > > > ActiveWorkbook.Activate
> > > > End If
> > > >
> > > > Let me know if this is not exactly what you were trying to accomplish.
> > > > --
> > > > Happy Coding,
> > > >
> > > > Scott
> > > >
> > > >
> > > > "Mo" wrote:
> > > >
> > > > > I want the macro to give the user the option (yes or no) after the message of
> > > > > "Save File As ...". The macro appear below. Any suggestions will be
> > > > > greatly appreciated.
> > > > >
> > > > > > Sub cmdExit_Click()
> > > > > > Dim Response As String
> > > > > > Dim msg As String
> > > > > > Dim Style As String
> > > > > > Dim sPath As String
> > > > > > Dim sFilename As String
> > > > > > Dim ans
> > > > > >
> > > > > > msg = "Are you sure you want to Exit the application and Close Excel?"
> > > > > > Style = vbYesNo + vbInformation + vbDefaultButton2
> > > > > >
> > > > > > Response = MsgBox(msg, Style)
> > > > > > If Response = vbYes Then
> > > > > > sPath = "C:\MetroWest\"
> > > > > > sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
> > > > > > "mm-dd-yyyy")
> > > > > > ans = MsgBox("Save File As " & sFilename)
> > > > > > If ans = vbOK Then
> > > > > > ActiveWorkbook.SaveAs sPath & sFilename
> > > > > > ActiveWorkbook.Close savechanges:=True
> > > > > > Application.Exit
> > > > > > Application.StatusBar = "Application Closing."
> > > > > > End If
> > > > > > Else
> > > > > > ActiveWorkbook.Activate
> > > > > > End If
> > > > > >
> > > > > > End Sub
> > > > >