Re: [libreoffice-users] Renaming Tabs in a spreadsheet in bulk.

Hi Drew :)
Thanks for that.  Some of it even makes sense to me.  It looks completely different from the excel 
one.  A fresh re-write rather than a translation.  I'm hoping that a few more of these real-world 
examples will help me make sense of the various guides on macros someday.
Thanks and regards from
Tom :)


--- On Fri, 3/2/12, drew <drew@baseanswers.com> wrote:

From: drew <drew@baseanswers.com>
Subject: Re: [libreoffice-users] Renaming Tabs in a spreadsheet in bulk.
To: users@global.libreoffice.org
Date: Friday, 3 February, 2012, 22:14

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

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

> 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: users+help@global.libreoffice.org
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: users+help@global.libreoffice.org
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