My macro:
- I run it from a new spreadsheet.ods, which is designated oTargetDoc
- Opens an existing spreadsheet called portfolio.xls
- Copies a range from portfolio.xls into the new.ods (oTargetDoc)
- Manipulates the range – deletes columns, sorts, condenses duplicates into a summed cell
- Ending with dates in Column A, beginning at A2, and numbers in Column B. The number of rows depends on portfolio.xls data, which varies from one day to another.
The objective is to make a chart from the data in A2:last used cell, a column chart with the date (column A) on the X axis.
The macro does the job, but I get some weird results which I would like to ask about.
Here’s the code from the point where it is supposed to make a chart:
’ Find the last used cell ’
oCellCursor.gotoEndOfUsedArea(True)
oCellRangeAddress = oCellCursor.getRangeAddress()
RowNumber = oCellRangeAddress.EndRow
EndCell=TargetSheet.getCellByPosition(1,RowNumber)
EndRange=EndCell.AbsoluteName
Print EndRange
document = oTargetDoc.CurrentController.Frame Rem This is how to make TargetDoc the active doc
rem document = ThisComponent.CurrentController.Frame chooses the wrong spreadsheet
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = “ToPoint”
args3(0).Value = “$A$2:”&“EndRange”
dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:InsertObjectChart”, “”, 0, Array())
Question 1:
The line: Print EndRange I put in to make sure it was getting the right cell address. It does give the correct cell address, except that it
(a) Comes in a “Warning” box and
(b) gives the address as $Sheet1.$B$18. Which is a bit weird, because there is only one sheet in oTargetDoc
Why the warning box?
Question 2:
It correctly makes the chart, except that it requires me to click the Finish button of the chart wizard. How can I automate this step?
Question 3:
The last used cell is B18.
If I go to A25 or above and type in “xyz”, the chart changes such that instead of showing the columns scaled by date, the columns are now equidistant and the chart is extended to the right.
If instead of “xyz” I type in a date, the columns are still scaled by date but the chart is again extended to the right.
What is going on here?
Question 4.
If I now go and look at portfolio.xls, the spreadsheet from which the data was taken, there is an Information box which says “Invalid range”. Why?
I am attaching the output from the macro.
The thing works, more or less, so I’m not really concerned, but I am curious if anyone has answers to these questions. (I hardly need to add I am an amateur at this, and apologies for my ignorance).
output 2024-06-04.ods (55.2 KB)