Renaming Tabs in a spreadsheet in bulk.

Gordon_Burgess-Park1 · February 3, 2012, 3:05pm

I have a spreadsheet that is used for monthly and annual collection of data, with monthly tabs, Jan 2011, Feb 2011 etc.
Is there a way to rename 2011 to 2012 in bulk, or do I have to do each one manually?

Villeroy · February 3, 2012, 3:50pm

Am 03.02.2012 16:05, Gordon Burgess-Parker wrote:

I have a spreadsheet that is used for monthly and annual collection of
data, with monthly tabs, Jan 2011, Feb 2011 etc.
Is there a way to rename 2011 to 2012 in bulk, or do I have to do each
one manually?

Splitting up equally structured data across many sheets is *always* a huge mistake. Don't do that.
Simply put everything in one table and add a field for the month.
There are dozends of features which allow you to get data and calculations for any category of a single table.

Tom_Davies · February 3, 2012, 3:50pm

Hi
Have you opened the ods as an archived-file / zip-file? Are the tab-labels in the contents.xml in there?
Regards from
Tom

Gordon_Burgess-Park1 · February 3, 2012, 4:06pm

I tried adding this Macro:

Sub Rename_Tabs()
  Dim x As Long, suffix As String
  Dim v as Variant
  suffix = " 12"
  v = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
*For x = 1 To Worksheets.Count*
      If Not IsError(Application.Match(Left(Sheets(x).Name, 3), v, 0)) Then
      Sheets(x).Name = Left(Sheets(x).Name, 3) & suffix
      End If
  Next
End Sub

But it fell over on the bolded portion. I'm not a macro person and this was copied from one in Excel that worked OK.
Anyone tell me what I need to replace the bolded bit with?

Hladuvka_Jiri · February 3, 2012, 5:18pm

Dne 3.2.2012 16:50, Andreas Säger napsal(a):

Am 03.02.2012 16:05, Gordon Burgess-Parker wrote:

I have a spreadsheet that is used for monthly and annual collection of
data, with monthly tabs, Jan 2011, Feb 2011 etc.
Is there a way to rename 2011 to 2012 in bulk, or do I have to do each
one manually?

Splitting up equally structured data across many sheets is *always* a huge mistake. Don't do that.
Simply put everything in one table and add a field for the month.
There are dozends of features which allow you to get data and calculations for any category of a single table.

Listen to that smart advice from Andreas.
And do it better by adding another field (column) for year and line_ID.

That line_ID I mean something that identifies the line content.
Let's say you have in each sheet report line labeled "Turnover", line labeled "Costs"
and line labeled "Number of employes".
Those line labels can be the line_ID but better is to add a numeric key because of sorting.

Then use the data-pilot to filter and select desired year and month.
The final report you can edit and format using the marvelous VLOOKUP function
- lookup in the data-pilot values belonging to line_IDs.

Resume:
- keep unformatted data in Sheet1 - this is your database

- have selected time period filtered in datapilot in Sheet2 - define once, change filter only

- format a nice presentation of v-looked-up values in Sheet3 - define once - it presents data of Sheet2

Regards,
Jiri

Stefan_Weigel · February 3, 2012, 6:10pm

Hi,

Am 03.02.2012 16:05, Gordon Burgess-Parker wrote:

I have a spreadsheet that is used for monthly and annual
collection of
data, with monthly tabs, Jan 2011, Feb 2011 etc.
Is there a way to rename 2011 to 2012 in bulk, or do I have to do
each
one manually?

Splitting up equally structured data across many sheets is *always*
a huge mistake. Don't do that.

Yes, can´t confirm this strong enough!

Have a look into Calc Guide Chapter 8
(http://www.libreoffice.org/get-help/documentation/#cg), especially
read the third rule on page 24.

Cheers,
Stefan

Gordon_Burgess-Park1 · February 3, 2012, 7:02pm

And as an addendum, I have a similar sheet in Excel. I asked the same question in the MS Excel forum and was immediately given a macro by an Excel specialist to do this without any lecturing on my method of data collection...

Tom_Davies · February 3, 2012, 7:12pm

Hi
Yes, i think these guys are talking about data-storage and ignoring accountancy principles. Most places i have seen have different tabs for different months or for different quarters and that allows reports to show bank reconciliations, outstanding payments, prepayments, accruals for a set date without those figures then getting messed-up by subsequent postings.

It's just that we have had a lot of posts about databases recently which is an area we need to get more development work going on.
Apols and regards from
Tom

Gordon_Burgess-Park1 · February 3, 2012, 7:16pm

Thanks - if I'd wanted to create a database I wouldn't be using a spreadsheet! Base or Access would be my application of choice...

Villeroy · February 3, 2012, 7:26pm

Am 03.02.2012 20:02, Gordon Burgess-Parker wrote:

And as an addendum, I have a similar sheet in Excel. I asked the same
question in the MS Excel forum and was immediately given a macro by an
Excel specialist to do this without any lecturing on my method of data
collection...

This is not a rent-a-coder-for-nothing service where Calc specialists fix your broken data layout.

Gordon_Burgess-Park1 · February 3, 2012, 7:27pm

Sorry mate, my data IS NOT BROKEN.
Take your arrogant self serving attitude and STUFF IT WHERE THE SUN DOESN'T SHINE.

Tom_Davies · February 3, 2012, 7:27pm

Hi
So really all you need is a translation of the Excel macro below. Calc uses
a completely different language, or at least different enough that the below
coding wont work.
Regards from
Tom

Gordon Burgess-Parker wrote

Gordon_Burgess-Park1 · February 3, 2012, 7:29pm

*PLONK*.

MORON

Villeroy · February 3, 2012, 7:30pm

Am 03.02.2012 17:06, Gordon Burgess-Parker wrote:

But it fell over on the bolded portion. I'm not a macro person and this
was copied from one in Excel that worked OK.
Anyone tell me what I need to replace the bolded bit with?

Please, do us all a favour and run Excel.

Tom_Davies · February 3, 2012, 7:37pm

Hi
If one or 2 individuals can't do a neat macro they don't need to but other
people have been know to generously show they can do neat little bits for
people.
Regards from
Tom

Villeroy · February 3, 2012, 7:46pm

Am 03.02.2012 20:27, Tom wrote:

Hi
So really all you need is a translation of the Excel macro below. Calc uses
a completely different language, or at least different enough that the below
coding wont work.
Regards from
Tom

Meanwhile you should know that the language is more or less the same. The thing you talk to is rather different. You have to tell different things in the same language when you speak Basic to Excel.

drew · February 3, 2012, 7:57pm

Ah - yeah, there a couple of VBA -> LibOBasic gottyas there..

I don't mind transformig that snippet - have a conf. call starting in
just a couple of minutes, but if no one else has already done so by the
time I'm off that will post it up..

//drew

elderdanlewis · February 3, 2012, 8:18pm

Might there be another method? I just unzipped a Calc spreadsheet.
Then I looked at the Context.xml. I found:
"<table:table table:name =" (tab name) .
Could this help some?

--Dan

Stefan_Weigel · February 3, 2012, 8:24pm

Hi Gordon,

Hi
Have you opened the ods as an archived-file / zip-file? Are the
tab-labels in the contents.xml in there?
Regards from
Tom

Did you try Toms hint?

I tried adding this Macro:

Sub Rename_Tabs()
Dim x As Long, suffix As String
Dim v as Variant
suffix = " 12"
v = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
*For x = 1 To Worksheets.Count*
     If Not IsError(Application.Match(Left(Sheets(x).Name, 3), v,
0)) Then
     Sheets(x).Name = Left(Sheets(x).Name, 3) & suffix
     End If
Next
End Sub

But it fell over on the bolded portion. I'm not a macro person and
this was copied from one in Excel that worked OK.
Anyone tell me what I need to replace the bolded bit with?

(a) Do you want to have this question answered? Or (b) do you want
to be helped with your problem?

If (a) then replace
For x = 1 To Worksheets.Count
by
For x = 0 To thisComponent.Sheets.count-1

If (b) please recognize that your problem derives from an
unfortunate data layout, just as Andreas and Jiri have been pointing
out. However you could try to handle your situation by either
replacing the relevant values inside the content.xml, just as Tom
has been indicating. This can be done by the Search&Replace
functionality of any simple text editor. Or, if you would rather
have a BASIC macro doing the job, you will need to dive into the
StarOffice object modell, which is very different to Excel, which is
why it´s little help to present an Excel macro for this task.

http://wiki.documentfoundation.org/Macros could be a starting point.

Regards,
Stefan

drew · February 3, 2012, 10:14pm

> Hi
> Have you opened the ods as an archived-file / zip-file? Are the tab-labels in the contents.xml in there?
> Regards from
> Tom
>
>
>

I tried adding this Macro:

Sub Rename_Tabs()
  Dim x As Long, suffix As String
  Dim v as Variant
  suffix = " 12"
  v = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
*For x = 1 To Worksheets.Count*
      If Not IsError(Application.Match(Left(Sheets(x).Name, 3), v, 0)) Then
      Sheets(x).Name = Left(Sheets(x).Name, 3) & suffix
      End If
  Next
End Sub

Ok - well, here is a quick replacement, should do the trick for you

Sub Rename_Tabs()
  Dim x As Long, suffix As String
  Dim v as Variant
  Dim CurrentFile as object
  Dim WorkSheets as Variant

  ' LibreOffice ships with a collection of useful macros in a libary
  ' TOOLS which must be loaded explicity
  BasicLibraries.loadLibrary("Tools")

suffix = " 12"
v = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")

  CurrentFile = thisComponent
  ' thisComponent is a pseudo variable that always returns the current
  ' document on your desktop

  ' from here on out it should be fairy clear
  '
  WorkSheets = CurrentFile.getSheets

  For x = 0 To Worksheets.Count -1

    ' the IndexinArry function in 'tools' is similar to MATCH in VBA
    If IndexinArray(Left(WorkSheets(x).Name, 3), v) <> -1 Then
      WorkSheets(x).Name = Left(WorkSheets(x).Name, 3) & suffix
    End If
  Next

End Sub

Best of luck,

//drew