Reference a cell as part of a file name
 

I'm trying to figure out how I can have a macro save an Excel 2000 Worksheet
and get the name of the worksheet from a cell reference, i.e. FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

You can get the date from VBA

Activeworkbook.SaveAs Filename:="Filename" & Format(Date,"mm-dd-yyyy") &
".xls"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

Hi YokerBrokerBoy,

Try something like:

'===============================
Public Sub Tester03()
Dim sStr As String
Dim sStr2 As String
Dim Pos As Long
Dim blValid As Boolean

With ActiveSheet.Range("A1")
If Not IsEmpty(.Value) Then
If IsDate(.Value) Then
blValid = True
sStr = Format(.Value, " (dd-mm-yyyy) ")

With .Parent.Parent
Pos = InStr(1, .Name, ".", vbTextCompare)
If Pos 0 Then
sStr2 = Left(.Name, Pos - 1)
Else
sStr2 = .Name
End If

.SaveAs sStr2 & sStr & ".xls"

End With
End If
End If
End With

If Not blValid Then MsgBox _
prompt:="No date found in A1, file not saved!", _
Buttons:=vbCritical, _
Title:="File NOT saved!"

End Sub
'===============================

---
Regards,
Norman



"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

Hi YokerBrokerBoy,

Change:

sStr = Format(.Value, " (dd-mm-yyyy) ")

to:

sStr = Format(.Value, " (mm-dd-yyyy) ")


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi YokerBrokerBoy,

Try something like:

'===============================
Public Sub Tester03()
Dim sStr As String
Dim sStr2 As String
Dim Pos As Long
Dim blValid As Boolean

With ActiveSheet.Range("A1")
If Not IsEmpty(.Value) Then
If IsDate(.Value) Then
blValid = True
sStr = Format(.Value, " (dd-mm-yyyy) ")

With .Parent.Parent
Pos = InStr(1, .Name, ".", vbTextCompare)
If Pos 0 Then
sStr2 = Left(.Name, Pos - 1)
Else
sStr2 = .Name
End If

.SaveAs sStr2 & sStr & ".xls"

End With
End If
End If
End With

If Not blValid Then MsgBox _
prompt:="No date found in A1, file not saved!", _
Buttons:=vbCritical, _
Title:="File NOT saved!"

End Sub
'===============================

---
Regards,
Norman



"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

BTW, putting a date in a filename probably best to put it at front, and
format as yyyy-mm-dd, for sorting

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You can get the date from VBA

Activeworkbook.SaveAs Filename:="Filename" & Format(Date,"mm-dd-yyyy")
&
".xls"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

Hi Bob,

Agreed.

Despite the OP's explicit format request, I had intended to indicate the
potential sort order advantages, but forgot to do so.

Certainly, if the files were mine, I would adopt the yyyy-mm-dd format.

Thank you.

---
Regards,
Norman



"Bob Phillips" wrote in message
...
BTW, putting a date in a filename probably best to put it at front, and
format as yyyy-mm-dd, for sorting

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You can get the date from VBA

Activeworkbook.SaveAs Filename:="Filename" &
Format(Date,"mm-dd-yyyy")
&
".xls"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

Hi Norman,

I forgot also (as usual), having to dash off a quick follow-up.

Bob




"Norman Jones" wrote in message
...
Hi Bob,

Agreed.

Despite the OP's explicit format request, I had intended to indicate the
potential sort order advantages, but forgot to do so.

Certainly, if the files were mine, I would adopt the yyyy-mm-dd format.

Thank you.

---
Regards,
Norman



"Bob Phillips" wrote in message
...
BTW, putting a date in a filename probably best to put it at front, and
format as yyyy-mm-dd, for sorting

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You can get the date from VBA

Activeworkbook.SaveAs Filename:="Filename" &
Format(Date,"mm-dd-yyyy")
&
".xls"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as part of a file name
 

Bob & Norm... Thanks for the great input. I like the short & sweet approach;
will try Norm's as well a bit later. - Ian (Yobrokerboy)

"Bob Phillips" wrote:

BTW, putting a date in a filename probably best to put it at front, and
format as yyyy-mm-dd, for sorting

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You can get the date from VBA

Activeworkbook.SaveAs Filename:="Filename" & Format(Date,"mm-dd-yyyy")
&
".xls"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yobrokerboy" wrote in message
...
I'm trying to figure out how I can have a macro save an Excel 2000
Worksheet
and get the name of the worksheet from a cell reference, i.e.
FileNameDATE,
where DATE is the contents of a cell generated as =TODAY() in format
mm-dd-yyyy. For this discussion, the date is in cell A1.

Any and all input is greatly appreciated.

Reference a cell as the file name
 

I want to do something very similar to this. I made a template for my dad
with 3 different sheets. He will be using only one of these sheets each time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the file
name?

Thanks,
Cory

Reference a cell as the file name
 

Hi PromiscuousWoman,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("A1") '<<==== CHANGE

ActiveWorkbook.Savas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============


Change the sheet name and the range to accord with your needs.

---
Regards,
Norman



"promiscuousman" wrote in message
...
I want to do something very similar to this. I made a template for my dad
with 3 different sheets. He will be using only one of these sheets each
time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the
file
name?

Thanks,
Cory

Reference a cell as the file name
 

This is the macro I eneded up creating
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("B20") '<<==== CHANGE

ActiveWorkbook.Saveas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
B20 is the cell I wish to save as, in sheet one, but if they use sheet two
instead it's a different cell (they will never use both sheets in the
workbook at the same time) is there a way to make it recognize the active
sheet, and base the cell number off of the sheet name?
Admittedly I don't know much about this code stuff, I took an intro class to
it, and I've got an idea how it works, declaring variables and such, and I
can kind of follow a code somebody else wrote, and see what it's doing, but I
won't be able to write my own, yet.
Thanks for the help,
Cory
"Norman Jones" wrote:

Hi PromiscuousWoman,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("A1") '<<==== CHANGE

ActiveWorkbook.Savas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============


Change the sheet name and the range to accord with your needs.

---
Regards,
Norman



"promiscuousman" wrote in message
...
I want to do something very similar to this. I made a template for my dad
with 3 different sheets. He will be using only one of these sheets each
time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the
file
name?

Thanks,
Cory

Reference a cell as the file name
 

Hi Cory,

Firstly. apologies for the unintententional attempt to change your gender!

If I understand your intentions, and assuming that the address of the cell
of interest is constant on each sheet, try replacing the sheet reference
with the ActiveSheet property:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = ActiveSheet.Range("B20")

ActiveWorkbook.SaveAs _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============

---
Regards,
Norman



"promiscuousman" wrote in message
...
This is the macro I eneded up creating
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("B20") '<<==== CHANGE

ActiveWorkbook.Saveas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
B20 is the cell I wish to save as, in sheet one, but if they use sheet two
instead it's a different cell (they will never use both sheets in the
workbook at the same time) is there a way to make it recognize the active
sheet, and base the cell number off of the sheet name?
Admittedly I don't know much about this code stuff, I took an intro class
to
it, and I've got an idea how it works, declaring variables and such, and I
can kind of follow a code somebody else wrote, and see what it's doing,
but I
won't be able to write my own, yet.
Thanks for the help,
Cory
"Norman Jones" wrote:

Hi PromiscuousWoman,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("A1") '<<==== CHANGE

ActiveWorkbook.Savas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============


Change the sheet name and the range to accord with your needs.

---
Regards,
Norman



"promiscuousman" wrote in
message
...
I want to do something very similar to this. I made a template for my
dad
with 3 different sheets. He will be using only one of these sheets
each
time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the
file
name?

Thanks,
Cory

Reference a cell as the file name
 

Haha, I didn't even notice that actually. The address of the cell is
actually different between sheet 1 and sheet 2, but that can easily be
changed since the first 15 rows are so are just filler, and I can add some
more to one of them and make them narrower so they still fit on one page and
the cell number will be the same. I'll give this a try now, and reassign the
button i've created to this macro instead.
Thank you for your help,
Cory

"Norman Jones" wrote:

Hi Cory,

Firstly. apologies for the unintententional attempt to change your gender!

If I understand your intentions, and assuming that the address of the cell
of interest is constant on each sheet, try replacing the sheet reference
with the ActiveSheet property:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = ActiveSheet.Range("B20")

ActiveWorkbook.SaveAs _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============

---
Regards,
Norman



"promiscuousman" wrote in message
...
This is the macro I eneded up creating
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("B20") '<<==== CHANGE

ActiveWorkbook.Saveas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
B20 is the cell I wish to save as, in sheet one, but if they use sheet two
instead it's a different cell (they will never use both sheets in the
workbook at the same time) is there a way to make it recognize the active
sheet, and base the cell number off of the sheet name?
Admittedly I don't know much about this code stuff, I took an intro class
to
it, and I've got an idea how it works, declaring variables and such, and I
can kind of follow a code somebody else wrote, and see what it's doing,
but I
won't be able to write my own, yet.
Thanks for the help,
Cory
"Norman Jones" wrote:

Hi PromiscuousWoman,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("A1") '<<==== CHANGE

ActiveWorkbook.Savas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============


Change the sheet name and the range to accord with your needs.

---
Regards,
Norman



"promiscuousman" wrote in
message
...
I want to do something very similar to this. I made a template for my
dad
with 3 different sheets. He will be using only one of these sheets
each
time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the
file
name?

Thanks,
Cory

Reference a cell as the file name
 

Thanks again for the help. I got it to work out that way, I made it so both
sheets had the desired filename in the same address, and also managed to
create a shortcut button and put it on the tool bar. This will be very
helpful for my dad.

Thank you,
Cory

"promiscuousman" wrote:

Haha, I didn't even notice that actually. The address of the cell is
actually different between sheet 1 and sheet 2, but that can easily be
changed since the first 15 rows are so are just filler, and I can add some
more to one of them and make them narrower so they still fit on one page and
the cell number will be the same. I'll give this a try now, and reassign the
button i've created to this macro instead.
Thank you for your help,
Cory

"Norman Jones" wrote:

Hi Cory,

Firstly. apologies for the unintententional attempt to change your gender!

If I understand your intentions, and assuming that the address of the cell
of interest is constant on each sheet, try replacing the sheet reference
with the ActiveSheet property:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = ActiveSheet.Range("B20")

ActiveWorkbook.SaveAs _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============

---
Regards,
Norman



"promiscuousman" wrote in message
...
This is the macro I eneded up creating
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("B20") '<<==== CHANGE

ActiveWorkbook.Saveas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
B20 is the cell I wish to save as, in sheet one, but if they use sheet two
instead it's a different cell (they will never use both sheets in the
workbook at the same time) is there a way to make it recognize the active
sheet, and base the cell number off of the sheet name?
Admittedly I don't know much about this code stuff, I took an intro class
to
it, and I've got an idea how it works, declaring variables and such, and I
can kind of follow a code somebody else wrote, and see what it's doing,
but I
won't be able to write my own, yet.
Thanks for the help,
Cory
"Norman Jones" wrote:

Hi PromiscuousWoman,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = Sheets("Sheet1").Range("A1") '<<==== CHANGE

ActiveWorkbook.Savas _
Filename:=rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============


Change the sheet name and the range to accord with your needs.

---
Regards,
Norman



"promiscuousman" wrote in
message
...
I want to do something very similar to this. I made a template for my
dad
with 3 different sheets. He will be using only one of these sheets
each
time
the template is opened, and saving it as a number entered in one of the
cells. How would I create a macro that will put the cell value as the
file
name?

Thanks,
Cory