Put a value in a multiple range cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
mninups
Posts: 5
Joined: Sat Jan 07, 2017 9:47 pm

Put a value in a multiple range cell

Post by mninups »

Hello,
And sorry, but after a long time of searching and trials this Question :(
My text looks a little bit to long, but only because I show another way to explain in.
I'm writing a macro, that adds several values each into a range(=timeline) from a given Startdate to an Enddate:
That's my idea:

1. At first search the given Startdate and given Enddate in the timeline. Result: Startdate="A3" and Enddate="A10".
2. ...And add the value just once in the rang (without a Loop). With VBA it would be something like this:
Worksheets(sheet1").Range("A3:A10").Value = Value1

Is that possible? I found several suggestions in the Internet, but nothing worked with Basic.
I also already did it with a Loop - but the result was too slowly if I want to do this with a very long timeline and for many different values and properties at the same time.
Maybe the following sentence is really true and everything I mad with basic before was for nothing and I could not use OpenOffice-Basic for this?
"I know that MS Excel lets you assign a scalar value to a range object and this will be internally transformed into assigning each cell in this range that value. This is not possible in OOo Calc!."


With a Loop (For a better understanding):
'1.At first I make/have a timeline for a range of cells:
A1= "01/01/2015 00:00:00", A2= "01/01/2015 00:15:00", ... , A3= "12/31/2016 23:45:00"
'2....Than for each value I search the start-date-cell in this timeline with a while-loop:
While sheet1.getCellByPosition(0,i_row_date).string <> start_date_value_x
i_zeile_date = i_zeile_date +1
Wend
'3....and add the value for each period of the timeline into a cell of a column beside it until the end-date-cell occures:
While sheet1.getCellByPosition(0,i_row_date).string <> end_date_value_x
sheet1.getCellByPosition(1,i_row_date).string = Val(Value_x) + Val(tabelle1.getCellByPosition(1,i_row_date).string)
i_zeile_date = i_zeile_date +1
Wend

Thank you for your help!
LibreOffice 5.0.6.2
Ubuntu 15.10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: put a value in a multiple range cell

Post by Zizi64 »

Please upload your real .ods type example spreadsheet here.

And please update your signature:
OpenOffice 2.4
Ubuntu 15.10
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
mninups
Posts: 5
Joined: Sat Jan 07, 2017 9:47 pm

Re: Put a value in a multiple range cell

Post by mninups »

Thank you for your answer.
Create_a_timeseries.ods
The simplified spreadsheet but not with the 40.000 rows.
(71.39 KiB) Downloaded 351 times
Create_a_timeseries.odt
And this is the attached Macro-Basic
(17.78 KiB) Downloaded 339 times
Again, I hope that there is a solution to work without the Loops to serch the right cell and instead do easily something like this:

1. Find-Command for the given Startdate and given Enddate in the timeline-row.
2. After knowing the Startdate and Enddate, add the value just once in the rang (without a Loop) from the startdate to the enddate. With VBA it would be something like this:
Worksheets(sheet1").Range("A2:A10").Value = Value_x
Last edited by mninups on Sun Jan 08, 2017 4:04 pm, edited 1 time in total.
LibreOffice 5.0.6.2
Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Put a value in a multiple range cell

Post by Villeroy »

First of all, your data are invalid because they are all text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mninups
Posts: 5
Joined: Sat Jan 07, 2017 9:47 pm

Re: Put a value in a multiple range cell

Post by mninups »

Hello Villeroy, thank you for your answer.

I used
Value = Val(Value)
DateSerial(Year(),Month(),Day())
TimeSerial(Hour(),Minute(),Second())
to change them.
My Macro-Code ("Create_a_timeseries.odt") realy works, it is just too slowly for many rows and I need another solution.

Is there nothing like this VBA-Code in Openoffice? Worksheets(sheet1").Range("A2:A10").Value = Value
Last edited by mninups on Sun Jan 08, 2017 4:27 pm, edited 1 time in total.
LibreOffice 5.0.6.2
Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Put a value in a multiple range cell

Post by Villeroy »

The value of a text cell is zero.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mninups
Posts: 5
Joined: Sat Jan 07, 2017 9:47 pm

Re: Put a value in a multiple range cell

Post by mninups »

I'm sorry, im a greenhorn....but I want to learn how it works. So for me at first it looks like that it doesnt matter if it is text, because its possible to switch it to a value or date/time with the code of my last post.
LibreOffice 5.0.6.2
Ubuntu 15.10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Put a value in a multiple range cell

Post by Zizi64 »

All of the data are strings but not numeric values in your example file.

The dates (the real dates) are numeric values with a specific formatting property.

The DateTime values are floating point numbers.
The integer part of the number represents the date value (in unit 'Days') - relative to a conventional base date. Due to compatibility reasons (with some other older spreadsheet, and database softwares) there are three differendt base date in the AOO and LO. You can choose one in the Tools - Option - Open/LibreOffice Calc - Calculate - Date... The default one is 1899 december
And the decimal fraction of the number represents the hours minutes and seconds as a decimal fraction part of a day. Read: 1.0 day is 24 hour, therefore 0.5 day means 12:00.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Put a value in a multiple range cell

Post by Villeroy »

If your programming skills are that low then I suppose you try to do this stuff with Excel/VBA. I won't teach you programming.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Put a value in a multiple range cell

Post by FJCC »

When working with large sets of data, it is much faster to move the data into an array in the code and write it back out to the spreadsheet at the end. This avoids many API calls that slow down execution. I made an example of searching for start and end dates and writing a number in the adjoining column within the date range. An important difference to what you have is that I used numeric time stamps and not strings as in your data. I looped through the array to find the start and end time stamps to show that this can be done quickly if one avoids the API calls. Since the time stamps are equally spaced, it would make more sense to calculate the location of the start and end cells from the known beginning of the time series.

Code: Select all

Sub Main
oSheet = ThisComponent.Sheets.getByName("Sheet1")
Rng = oSheet.getCellrangeByName("A1:A14496")
StartDateCell= oSheet.getCellrangeByName("C1")
StartDateValue= StartDateCell.Value
EndDateCell= oSheet.getCellrangeByName("D1")
EndDateValue= EndDateCell.Value
DataArry = Rng.getDataArray()

i = 0
StartLoc = 0
EndLoc = 0
Flag = 0
While Flag = 0
	'the 0.001 accounts for rounding error in the time series
	If abs(DataArry(i)(0) - StartDateValue) < 0.001 Then  
		StartLoc = i
	End If
	
	If abs(DataArry(i)(0) - EndDateValue) < 0.001 Then
		EndLoc = i
		Flag = 1
	End If
	i = i + 1
WEnd

'Fill Colummn B with 1 within the date range
Rng2 = oSheet.getCellrangeByPosition(1,StartLoc, 1, EndLoc)

DataArry2 = Rng2.DataArray
for i = 0 to (EndLoc - StartLoc)
	DataArry2(i)(0) = 1
next i
Rng2.DataArray = DataArry2

End Sub
Attachments
Timeseries.ods
(158.83 KiB) Downloaded 365 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Put a value in a multiple range cell

Post by Villeroy »

But how can he work with Basic arrays if he can not even distinguish text from numbers?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Put a value in a multiple range cell

Post by Zizi64 »

The Office suite can recognize and convert the date like strings (for example this string: 2017-01-08 15:48) to numeric values at the moment when you type-in or paste them into the cells.
Cut (Ctrl-A / Ctrl-X) the strings and paste them again into the original cellrange as unformatted text. Adjust the locale settings of the columns of the input filter - if it is necessary.
Maybe the numeric values will be appeared as decimal numbers without any formatting after the pasting. Do not worry. Just format the numeric cells as Date or Time or as combined DateTime.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
mninups
Posts: 5
Joined: Sat Jan 07, 2017 9:47 pm

Re: Put a value in a multiple range cell

Post by mninups »

Zizi64 , FJCC, Zizi64 and Villeroy , thank you very much for your help! I think that I understand it now. :)
If I can support your openoffice community in way let me now.
I know that it is stupid, if greenhorns like me are asking questions without sufficient background. For me it is "learning by doing", but maybe Villeroy is right and I should make some basic courses to understand more of the fundamentals.
LibreOffice 5.0.6.2
Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Put a value in a multiple range cell

Post by Villeroy »

I'm 90% sure that you do not need any macros. However, I still do not understand what you are trying to achieve and how it is related to your list of date/time strings.
The main purpose of spreadsheet software since VisiCalc of 1979 is to help non-programmers with a simplified programming language so they can perform everyday calculation tasks (and some data processing) in a visual context.
And by the way: How do these strings get into your spreadsheet? Nobody typed this into a keyboard, so I think proper data import is the first problem to solve.
 Edit: P.S. I see that you are running Linux. There is no reason why you should learn programming in the awkward environment of an office suite when you have several modern, mature and easy to learn environments pre-installed on your system at zero cost. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply