Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference only part of a cell | Excel Discussion (Misc queries) | |||
Use worksheet name in one cell as part of reference in other cells | Excel Discussion (Misc queries) | |||
Reference to a Variable Part of a Cell | Excel Worksheet Functions | |||
Using a cell reference as part of a link | Excel Discussion (Misc queries) | |||
worksheet tab name as part of a cell reference | Excel Worksheet Functions |