Renaming Tabs in a spreadsheet in bulk.

classic Classic list List threaded Threaded
23 messages Options
Next » 12
Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Renaming Tabs in a spreadsheet in bulk.

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?

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
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 :)


--- On Fri, 3/2/12, Gordon Burgess-Parker <[hidden email]> wrote:

From: Gordon Burgess-Parker <[hidden email]>
Subject: [libreoffice-users] Renaming Tabs in a spreadsheet in bulk.
To: [hidden email]
Date: Friday, 3 February, 2012, 15:05

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?

-- For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

On 03/02/2012 15:50, Tom Davies wrote:
> 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

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?

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

"Ing. Jiří Hladůvka" "Ing. Jiří Hladůvka"
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Andreas Säger
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

--

Ing. Jiří Hladůvka - REVIDA

http://www.revida.sk
mailto:[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Andreas Säger
Hi,

Am 03.02.2012 16:50, schrieb Andreas Säger:

> 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


--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

On 03/02/2012 18:05, Stefan Weigel wrote:

> Hi,
>
> Am 03.02.2012 16:50, schrieb Andreas Säger:
>
>> 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
>
>
Yeah. OK. As someone who has been a Systems and Management Accountant
(NOT a pseudo-database administrator) for 30 years and has used
spreadsheets at advanced level with separate tabs for calendar-month
data (as have all the colleagues I have worked with over that period in
many different organisations) this is all very interesting but totally
irrelevant to my question.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

On 03/02/2012 18:59, Gordon Burgess-Parker wrote:

> On 03/02/2012 18:05, Stefan Weigel wrote:
>> Hi,
>>
>> Am 03.02.2012 16:50, schrieb Andreas Säger:
>>
>>> 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
>>
>>
> Yeah. OK. As someone who has been a Systems and Management Accountant
> (NOT a pseudo-database administrator) for 30 years and has used
> spreadsheets at advanced level with separate tabs for calendar-month
> data (as have all the colleagues I have worked with over that period
> in many different organisations) this is all very interesting but
> totally irrelevant to my question.
>

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...

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

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 :)



--- On Fri, 3/2/12, Gordon Burgess-Parker <[hidden email]> wrote:

From: Gordon Burgess-Parker <[hidden email]>
Subject: Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
To: [hidden email]
Date: Friday, 3 February, 2012, 19:02

On 03/02/2012 18:59, Gordon Burgess-Parker wrote:

> On 03/02/2012 18:05, Stefan Weigel wrote:
>> Hi,
>>
>> Am 03.02.2012 16:50, schrieb Andreas Säger:
>>
>>> 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
>>
>>
> Yeah. OK. As someone who has been a Systems and Management Accountant (NOT a pseudo-database administrator) for 30 years and has used spreadsheets at advanced level with separate tabs for calendar-month data (as have all the colleagues I have worked with over that period in many different organisations) this is all very interesting but totally irrelevant to my question.
>

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...

-- For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

On 03/02/2012 19:12, Tom Davies wrote:
> 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 :)
>
>

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...

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Andreas Säger
On 03/02/2012 19:25, Andreas Säger wrote:

> 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.
>
>
Sorry mate, my data IS NOT BROKEN.
Take your arrogant self serving attitude and STUFF IT WHERE THE SUN
DOESN'T SHINE.

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
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
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 .... I'm not a macro person and this was copied from one in Excel that worked OK.  Anyone tell me what  ... [Calc would need instead] ... ?
Gordon Burgess-Parker Gordon Burgess-Parker
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Andreas Säger
On 03/02/2012 19:25, Andreas Säger wrote:

> 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.
>
>

*PLONK*.

MORON

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

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 :)
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Tom
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.


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

drewjensen drewjensen
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
On Fri, 2012-02-03 at 16:06 +0000, Gordon Burgess-Parker wrote:

> On 03/02/2012 15:50, Tom Davies wrote:
> > 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
>
> 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?
>

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



--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Document Foundation Mail Archives
Dan Lewis Dan Lewis
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Andreas Säger
On Fri, 2012-02-03 at 20:45 +0100, Andreas Säger wrote:

> 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.

     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


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Stefan Weigel Stefan Weigel
Reply | Threaded
Open this post in threaded view
|

Re: Renaming Tabs in a spreadsheet in bulk.

In reply to this post by Gordon Burgess-Parker
Hi Gordon,

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

> On 03/02/2012 15:50, Tom Davies wrote:

>> 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



--
LibreOffice - Die Freiheit nehm' ich mir!

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Next » 12