[Solved] How to get Max of Dates in OpenOffice macro

Keyboard macros or custom scripts

[Solved] How to get Max of Dates in OpenOffice macro

Postby pranjal_ds » Sun Nov 15, 2020 7:28 am

Hi All,

I have written a macro in Apache Open Office 4.1.2.
I need code to calculate the max of Dates from a column of dates in 1 sheet and then copy the max value to another sheet cell.

Please let me know how I can do this?

Thank you.

Regards,
Last edited by robleyd on Mon Nov 16, 2020 12:07 pm, edited 1 time in total.
Reason: Tagged [Solved]
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am

Re: How to get Max of Dates in Open office macro

Postby RoryOF » Sun Nov 15, 2020 12:20 pm

How have you attempted this in your macro, and in what way does your code fail?
Apache OpenOffice 4.1.10 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32820
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to get Max of Dates in Open office macro

Postby pranjal_ds » Sun Nov 15, 2020 12:49 pm

I have a sheet named "Pranjal Shirsat" which is having dates in column K and have another sheet named "Summary" and I have to find the max date from sheet "Pranjal Shirsat" and paste it in "Summary" sheet cell L3.

I have written code to get the max in macro CreateSheets.

Code to find max is as below, but it is returning a fraction value in variable TheMax.
I am also attaching my file containing the macro herewith.
Please check and provide me resolution.

Code: Select all   Expand viewCollapse view
         dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())
         Cell = oSheet1.getCellByPosition(1,i)
         EmpName  = Cell.String() ' Get the employee name from empoyee name column of Summary sheet
         'msgbox EmpName
         oSheet2 = ThisComponent.Sheets.getByName(EmpName)
          theColumn = oSheet.Columns(10)
          eRgs = theColumn.queryEmptyCells
          u = eRgs.Count-1
          eRg = eRgs(u)
          iCell1 = eRg.getCellByPosition(0, 0)
          lCellAdd1 = iCell1.AbsoluteName
           arrAdd3 = split(iCell1.AbsoluteName,".")
          DPosStr1 = arrAdd3(1)
          DPosStr1 = mid(DPosStr1,2,Len(DPosStr1)
           'msgbox DPosStr1
          arrAdd3 = split(DPosStr1,"$")
          'msgbox arrAdd3(1)
          lRow3 = arrAdd3(1)  ' Get the New sheet last row of Date column
          Cell = oSheet1.getCellByPosition(11,i)
         
          svc = createUnoService("com.sun.star.sheet.FunctionAccess")
         'oSheet = ThisComponent.Sheets.getByName("Sheet1")
         oRng = oSheet2.getCellrangeByPosition(10,1,10,lRow3) '=A1:A11 (left, top,right,bottom)
         Data = oRng.Data
         TheMax = svc.callFunction("MAX",array(Data))
         msgbox TheMax

Attachments
Timeshet Details OpenOffice.ods
File containing the macro.
(40.91 KiB) Downloaded 121 times
Last edited by RoryOF on Sun Nov 15, 2020 12:59 pm, edited 2 times in total.
Reason: Added [code] tags [RoryOF, Moderator]
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am

Re: How to get Max of Dates in Open office macro

Postby RoryOF » Sun Nov 15, 2020 1:00 pm

I haven't time to examine at present, but I suspect you might need to format your Max value as a date.
Apache OpenOffice 4.1.10 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32820
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to get Max of Dates in Open office macro

Postby pranjal_ds » Sun Nov 15, 2020 1:03 pm

I used CDate(TheMax), but this returns 00/00/0000 which is not valid.

Regards,
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am

Re: How to get Max of Dates in Open office macro

Postby RoryOF » Sun Nov 15, 2020 1:50 pm

Your dates are showing up as text; use /Value /Highlighting to see the data types.
Apache OpenOffice 4.1.10 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32820
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to get Max of Dates in Open office macro

Postby Villeroy » Sun Nov 15, 2020 2:06 pm

The functionality for what you try is already built-in: Using the DataPilot (aka "Pivot Table")
Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.


[Tutorial] Date-Time Conversion in StarBasic
A US American M/D/Y string can only be converted if your office locale happens to be "English(USA)"
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
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to get Max of Dates in Open office macro

Postby pranjal_ds » Sun Nov 15, 2020 2:53 pm

Hi RoryOF,

Please let me know how to use the code you indicated in my macro?

Regards,
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am

Re: How to get Max of Dates in Open office macro

Postby Villeroy » Sun Nov 15, 2020 3:29 pm

In order to convert the US American date strings into valid spreadsheet dates (day numbers):
1) Select the cells in question
2) Right-click>Format... tab:Numbers,
Language: English(USA)
and any number format that is not text
3) menu:Find&Replace...
[More Options]
[X] Current Selection Only
[X] Regular Expressions
Search: .+ (a dot and a plus)
Replace: &
[Replace All]
This re-enters all strings into the modified language context of the selected cells.
Format the cells any way you like. The format does not change values
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
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to get Max of Dates in Open office macro

Postby RoryOF » Sun Nov 15, 2020 3:33 pm

The command I indicated is a setting in OpenOffice Calc to indicate the data types in the spreadsheet. Numbers will show as blue, text as black, results of formulae as green. Select /View /Value highlighting in the Calc menu and you will see that your "Dates" are all text; you need to convert these to proper dates so that at your macro will work.

See [Tutorial] Calc date formulas
for more information.
Apache OpenOffice 4.1.10 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32820
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to get Max of Dates in OpenOffice macro

Postby Villeroy » Sun Nov 15, 2020 6:27 pm

Any text to date conversion by formulas requires that you temporarily switch to the correct locale in Tools>Options>LanguageSettings>Languages. This is something that is not covered by the date formula tutorial. My above mentioned method with find&replace into the modified cell locale works just fine. Once you have correct numeric values, you can switch back any changed locale settings back to anything you prefer.
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
Villeroy
Volunteer
 
Posts: 30079
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to get Max of Dates in OpenOffice macro

Postby pranjal_ds » Mon Nov 16, 2020 7:13 am

Hi RoyOF/Vileroy,

In the below case I need to use Value() on a single cell but svc.CallFunction() accepts array as an input argument.
Please let me know how I can use VAlue function in svc.CallFunction.

Below code is giving error: "Basic runtime error" Object varaible not set"

svc = createUnoService("com.sun.star.sheet.FunctionAccess")
'oSheet = ThisComponent.Sheets.getByName("Sheet1")
oRng = oSheet2.getCellrangeByPosition(11,1,11,lRow3) '=A1:A11 (left, top,right,bottom)
Data = oRng.Data
TheMax = svc.callFunction("VALUE",array(Data))
msgbox TheMax
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am

Re: How to get Max of Dates in OpenOffice macro

Postby pranjal_ds » Mon Nov 16, 2020 11:59 am

Hi RoyOF/Vileroy,

I was able to generate the max of the dates in summary sheet by first converting the dates in cell range to long value and then finding hte max of the long value and then converting back the long value to date value.

This question is closed now.

Thank you.

Regards,
Apache Open Office 4.1.2, OS Windows 7
pranjal_ds
 
Posts: 11
Joined: Sun Nov 15, 2020 7:14 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests