Saving file using value in cell as the filename

BCassedy · External Since: Jul 05, 2007 Posts: 4

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"),TEXT(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.

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

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"),TEXT(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.

BCassedy · External Since: Jul 05, 2007 Posts: 4

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"),TEXT(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.
>
>

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

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"),TEXT(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.
>>
>>