Page 1 of 1
[Dropped] Macro to rename sheet from cell value
Posted: Sun Feb 12, 2017 10:01 am
by E. Smith
I am trying to create a macro that will rename the current sheet (Say Sheet 5) to the value of a cell in the current sheet
eg: in Cell A1 I have "2017"
in Cell B1 I have "1" (To represent the month of January)
in cell C1 I have "1" (To represent the 1st day of the month)
in cell D1 I have a Variable such as a,b,c,d etc. (Say I have "a")
in Cell E1 I have all the values above combined to give me a cell value of "1Jan2017a"
I want to be able to run a macro that will rename the current sheet with the value of cell E1, So the result will be a sheet called "1Jan2017a"
I have tried all the macros in this forum for similar topics but they have not worked.
Your help would be appreciated.
Thank-you
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 10:11 am
by robleyd
Disclaimer; I don't use macros.
It seems that the solution in
viewtopic.php?f=9&t=5693 is almost what you want. Just take out the loop and modify the cell addresses as needed.
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 4:13 pm
by E. Smith
I tried the editing the script mentioned in the link above but didn't work:
Sub Main
Dim oSheet
Dim oCell
oSheet = ThisComponent.Sheets
oCell = oSheet.getCellRangeByName("A1")
oSheet.Name = oCell.String
End Sub
Any other suggestions?
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 4:41 pm
by Zizi64
Which sheet? The .Sheets will get ALL of the Sheets of a document. You must choose ONE of them. You can get a sheet by name or by its number or you can get the actual active sheet.
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 5:37 pm
by Villeroy
Instead of macro programming, you'd better learn how to organize data efficiently so you can use this software (or any other software for that matter) without extra coding.
Getting information from separate lists is very difficult. Keeping multiple lists for the same class of information is one of the most toxic beginner's mistakes.
Look at one of my example spreadsheets:
download/file.php?id=23902
The first sheet named "Data" has 1000 records in
random order below a single row of column headers. This is called a "normalized list". Software can deal very well with normalized lists. You can insert a new row anywhere in that list in oder to add a new record. The position does not matter. Insert rows and keep the order of columns. Put dates below dates, values below values, names below names. Just avoid row #2 for insertion. Due to a design flaw, OpenOffice would copy the format attributes from the header row.
The header row includes auto-filter buttons. They help you to filter this list in place in order to see only the records you are interested in. This is way more convenient than finding the right sheet where to add/remove/edit the information. There are other filtering tools where you can specify multiple filter criteria. Sometimes you may want a certain category copied to a separate sheet. Even that is possible.
You can also click any single cell in that list and click one of the sort buttons [A-Z] or [Z-A] in order to sort the whole list by the clicked column.
More importantly a single normalized list allows you to aggregate your data by means of software tools. With a few clicks you can get the sum, count, average, min, max, median for any combination of years, quarters, months, days, weeks, category names or person names as demonstrated by the "Pilot" sheets. "Data Pilots", also known as "Pivot Tables" are described here:
https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Filtering, sorting, pivot tables, menu:Data>Subtotals ...,
database functions and various other spreadsheet functions (SUMIF, SUBTOTAL, SUMPRODUCT) let you derive information from a normalized list.
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 5:58 pm
by karolus
Hallo
+1 for Villeroy…
but anyway-
assuming cell E1 is already selected:
Code: Select all
sub rename_current_sheet()
doc = ThisComponent
cell = doc.CurrentSelection
sheet = cell.Spreadsheet
sheet.Name = cell.String
End Sub
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 7:30 pm
by DiGro
but anyway- assuming cell E1 is already selected:
but then again -
assuming cell E1 is NOT selected:
Code: Select all
sub rename_current_sheet()
Dim oDoc, oSheet, oCell
oDoc = ThisComponent
oSheet = oDoc.getCurrentController.getActiveSheet
oCell = oSheet.getCellByPosition(4,0) 'cell E1
oSheet.Name = oCell.String
End Sub
Re: Using a Macro to rename a sheet to a cell value
Posted: Sun Feb 12, 2017 7:34 pm
by RoryOF
Can you really not see the value to change? In the array that is the cells, all values start with 0, so 4,0 is E,1; A=0, B=1, C=2, D=3,E=4 and Row 1 in the spreadsheet maps to row 0 in the internal array.
Re: Using a Macro to rename a sheet to a cell value
Posted: Mon Feb 13, 2017 7:05 am
by E. Smith
Thanks for Trying But Neither of the two examples above work. I get a Basic Syntax error on line "doc = ThisComponent"
Re: Using a Macro to rename a sheet to a cell value
Posted: Mon Feb 13, 2017 8:48 am
by Zizi64
Please upload your example .ods file with the embedded macro code here.
Re: Using a Macro to rename a sheet to a cell value
Posted: Mon Feb 13, 2017 1:31 pm
by DiGro
Re: Using a Macro to rename a sheet to a cell value
Posted: Mon Feb 13, 2017 2:34 pm
by Villeroy
DiGro wrote:Works for me though
RenameSheet_test.ods
In order to make the green button functional:
View>Toolbars>Form Design
Toggle OFF the 2nd button from the left ("Toggle Design Mode")
Toggle OFF the 4th button from the right ("Open in Design Mode")
Save