[libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
Am 03.02.2012 21:18, Dan Lewis wrote:
Might there be another method? I just unzipped a Calc spreadsheet.
Then I looked at the Context.xml. I found:
"
Well, for someone who loves when others write free code, using a text
editor is totally inacceptable.
A double-click on the sheet tab is too much, same with Alt-click on the
sheet tab since you have to type the sheet name. All this is too
cumbersome for today's computer wimps. During the hours they beg for
code snippets they could rename thousands of sheets manually.
Now he has plonked me, I hope that he will not notice the following
snippet which does a better job for hundreds of sheets starting at the
active one.
Sub NameMonthSheets()
REM you may modify the following format string for the sheet names:
Const cOutFormat = "-MMM"
sm = InputBox("Starting with the active sheet, subsequent sheets will be
renamed to months like """& _
format(Now(),cOutFormat)&& chr(10)& _
"Please enter the start month as 2 numbers with a dash (-MM)","macro
RenameMonthSheets()", _
format(Now(),"-MM")
on error goto noInputErr
dStart = cdate(sm &"-01")
on error goto 0
im = Month(dStart)
iy = Year(dStart)
ish = ThisComponent.CurrentController.ActiveSheet.RangeAddress.Sheet
for i = ish to ThisComponent.Sheets.getCount()-1
x = im mod 12
if x = 0 then
x =12
elseif (x = 1)and(i > ish) then
iy = iy +1
endif
With ThisComponent.Sheets.getByIndex(i)
.setName(format(cDate(iy &"-"& x &"-01"),cOutFormat))
end with
im = im +1
next
noInputErr:
End Sub
--
For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: "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
[libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
[libreoffice-users] 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 :) -- View this message in context: http://nabble.documentfoundation.org/Renaming-Tabs-in-a-spreadsheet-in-bulk-tp3713315p3714085.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
[libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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] ... ?
>
--
View this message in context:
http://nabble.documentfoundation.org/Renaming-Tabs-in-a-spreadsheet-in-bulk-tp3713315p3714053.html
Sent from the Users mailing list archive at Nabble.com.
--
For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
[libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
Re: [libreoffice-users] 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: users+h...@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
Re: [libreoffice-users] 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 wrote: From: Gordon Burgess-Parker Subject: Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk. To: users@global.libreoffice.org 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: users+h...@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+h...@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
Re: [libreoffice-users] 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: users+h...@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
Re: [libreoffice-users] 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: users+h...@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
Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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: users+h...@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
Re: [libreoffice-users] Re: Renaming Tabs in a spreadsheet in bulk.
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:m...@revida.sk -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] 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: users+h...@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
