Page 1 of 1

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

PostPosted: Sun Nov 15, 2020 7:28 am
by pranjal_ds
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,

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

PostPosted: Sun Nov 15, 2020 12:20 pm
by RoryOF
How have you attempted this in your macro, and in what way does your code fail?

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

PostPosted: Sun Nov 15, 2020 12:49 pm
by pranjal_ds
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


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

PostPosted: Sun Nov 15, 2020 1:00 pm
by RoryOF
I haven't time to examine at present, but I suspect you might need to format your Max value as a date.

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

PostPosted: Sun Nov 15, 2020 1:03 pm
by pranjal_ds
I used CDate(TheMax), but this returns 00/00/0000 which is not valid.

Regards,

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

PostPosted: Sun Nov 15, 2020 1:50 pm
by RoryOF
Your dates are showing up as text; use /Value /Highlighting to see the data types.

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

PostPosted: Sun Nov 15, 2020 2:06 pm
by Villeroy
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)"

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

PostPosted: Sun Nov 15, 2020 2:53 pm
by pranjal_ds
Hi RoryOF,

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

Regards,

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

PostPosted: Sun Nov 15, 2020 3:29 pm
by Villeroy
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

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

PostPosted: Sun Nov 15, 2020 3:33 pm
by RoryOF
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.

Re: How to get Max of Dates in OpenOffice macro

PostPosted: Sun Nov 15, 2020 6:27 pm
by Villeroy
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.

Re: How to get Max of Dates in OpenOffice macro

PostPosted: Mon Nov 16, 2020 7:13 am
by pranjal_ds
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

Re: How to get Max of Dates in OpenOffice macro

PostPosted: Mon Nov 16, 2020 11:59 am
by pranjal_ds
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,