Saving file using value in cell as the filename
 

I've researched this for some time without success (or because I'm not as
well versed in Excel, may have overlooked it...)
I'm trying to set up a trouble ticket spread sheet where there's minimal
technical input required by the user:
in Cell B3 = current date.
in Cell G1 = following formula (used to create a "trouble ticket number" -
="301"&CONCATENATE(MONTH(B3),TEXT(DAY(B3),"00"),TE XT(YEAR(B3),"00"))
in Cell E14 I want to put a Command button which, when activated by the
user, will:
1. Save the file to a certain location on the hard drive (C:\OL Trbl
Tkts) -and-
use the value in Cell G1 (now reading 30107052007)
- which I'd like to cut down to 301070507 <ie. use only the
last 2 digits
of the year
as the file name.

ie. the result upon clicking the command button on July 6, 2007 would result
in the current new worksheet being saved as C:\OL Trbl Tkts\301070607 <in an
ideal situation -or- C:\OL Trbl Tkts\30107062007 as it now stands.

Saving file using value in cell as the filename
 

Sub Make_New_Book()
Dim mypath As String
mypath = "C:\OL Trbl Tkts"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=mypath & "\" & _
ActiveSheet.Range("G1").Value

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

I've researched this for some time without success (or because I'm not as
well versed in Excel, may have overlooked it...)
I'm trying to set up a trouble ticket spread sheet where there's minimal
technical input required by the user:
in Cell B3 = current date.
in Cell G1 = following formula (used to create a "trouble ticket number" -
="301"&CONCATENATE(MONTH(B3),TEXT(DAY(B3),"00"),TE XT(YEAR(B3),"00"))
in Cell E14 I want to put a Command button which, when activated by the
user, will:
1. Save the file to a certain location on the hard drive (C:\OL Trbl
Tkts) -and-
use the value in Cell G1 (now reading 30107052007)
- which I'd like to cut down to 301070507 <ie. use only the
last 2 digits
of the year
as the file name.

ie. the result upon clicking the command button on July 6, 2007 would result
in the current new worksheet being saved as C:\OL Trbl Tkts\301070607 <in an
ideal situation -or- C:\OL Trbl Tkts\30107062007 as it now stands.

Saving file using value in cell as the filename
 

Thank You!!!
I needed to modify the Sub name to reflect the use of a Command Button
(upon) Click:
Private Sub CommandButton1_Click()
, but once I did that, it works beautifully!

"Gord Dibben" wrote:

Sub Make_New_Book()
Dim mypath As String
mypath = "C:\OL Trbl Tkts"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=mypath & "\" & _
ActiveSheet.Range("G1").Value

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

I've researched this for some time without success (or because I'm not as
well versed in Excel, may have overlooked it...)
I'm trying to set up a trouble ticket spread sheet where there's minimal
technical input required by the user:
in Cell B3 = current date.
in Cell G1 = following formula (used to create a "trouble ticket number" -
="301"&CONCATENATE(MONTH(B3),TEXT(DAY(B3),"00"),TE XT(YEAR(B3),"00"))
in Cell E14 I want to put a Command button which, when activated by the
user, will:
1. Save the file to a certain location on the hard drive (C:\OL Trbl
Tkts) -and-
use the value in Cell G1 (now reading 30107052007)
- which I'd like to cut down to 301070507 <ie. use only the
last 2 digits
of the year
as the file name.

ie. the result upon clicking the command button on July 6, 2007 would result
in the current new worksheet being saved as C:\OL Trbl Tkts\301070607 <in an
ideal situation -or- C:\OL Trbl Tkts\30107062007 as it now stands.

Saving file using value in cell as the filename
 

Thanks for the feedback.

Gord

On Fri, 6 Jul 2007 06:08:02 -0700, BCassedy
wrote:

Thank You!!!
I needed to modify the Sub name to reflect the use of a Command Button
(upon) Click:
Private Sub CommandButton1_Click()
, but once I did that, it works beautifully!

"Gord Dibben" wrote:

Sub Make_New_Book()
Dim mypath As String
mypath = "C:\OL Trbl Tkts"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=mypath & "\" & _
ActiveSheet.Range("G1").Value

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 13:40:01 -0700, BCassedy
wrote:

I've researched this for some time without success (or because I'm not as
well versed in Excel, may have overlooked it...)
I'm trying to set up a trouble ticket spread sheet where there's minimal
technical input required by the user:
in Cell B3 = current date.
in Cell G1 = following formula (used to create a "trouble ticket number" -
="301"&CONCATENATE(MONTH(B3),TEXT(DAY(B3),"00"),TE XT(YEAR(B3),"00"))
in Cell E14 I want to put a Command button which, when activated by the
user, will:
1. Save the file to a certain location on the hard drive (C:\OL Trbl
Tkts) -and-
use the value in Cell G1 (now reading 30107052007)
- which I'd like to cut down to 301070507 <ie. use only the
last 2 digits
of the year
as the file name.

ie. the result upon clicking the command button on July 6, 2007 would result
in the current new worksheet being saved as C:\OL Trbl Tkts\301070607 <in an
ideal situation -or- C:\OL Trbl Tkts\30107062007 as it now stands.