LibreOffice - VBA - End(Xlup).offset(1,0) = Equivalaent in python

English
#1

Hi ,
I am now trying to adopt LO as my full time replacement to MS Excel, as I use VBA Macro in most of my work, I am stuck at this point for now,
in VBA , I was using
"example:

  1. Range(“A10”).End(Xlup).Offset(1,0)
  2. Range(“A10”).End(Xldown).Offset(1,0)
  3. Range(“A10”).End(XltoRight).Offset(1,0)
  4. Range(“A10”).End(XltoLeft).Offset(1,0)

to navigate , and find the last row , Right side, left side , I particularly use this function to do my thing, but i couldnt find and example, which is equivalent to this in Python , in LibreOffice.

If someone could help me , how to do the above four operations with Python in libreoffice with some example, it would be a great help to me.

please note : i have googled , binged, used duck duck go, but couldnt find the solution. please help me out , I am stuck.

#2

why the hell did you google, bing…whatever, but dont use a Objectinspector like MRI1.3.4.oxt??

#3

I used MRI, and I didnt understood anything, as I am not programmer, I am from Medical Profession, I use VBA to fasten, my medicine indent , consumption and patient record, So I had googled some examples of that, it would be kind of you if you show me how to do it, or give some links where i can learn some solution.

#4

Please, please, please. Stick with Xcel forever.

end(xlup)

#5

Mr. Villeroy, kindly Keep your suggestions to yourself.

Dear All,
Please if you don’t have answers, don’t give me useless suggestions,

I am only finding one thing, that is,
we can find the cell by position, by using getCellByPosition(1,1) which is equivalent to excels vba cells(1,1)

In the same way, I am finding how can we find the cell, which has data, and that I can offset to right and left, up and down.

#8

NO … The equivalent of vba: cells(1,1) is …getCellByPosition(0,0) and more important:

cells(3,1) →→→ ……byPosition(0,2) #realize the order of parameters

Change your attitude, if you think exclusively in ‘VBA-syntax’ you won’t be happy with the Libreoffice API and Python!

#9

I know the order, I have read the libreoffice, documentation, please read, I have written byposition(1,1) - - cells (1,1). I just used this to explain, what I wanted

#14

VBA obj.end(xlup) is an ugly internet meme from the 90ies like the evil clown or something. What’s the fuzz with that VBA meme? People try to get the last used row of a column. First they query the last cell of that column at the bottom of the sheet, then they do the VBA equivalent of hitting Ctrl+Up_Arrow. The position of the cell cursor marks the last used cell of that column and the cell below is the empty cell where they append a new record to their list.

There are several things wrong with that appropach. Most fatal may be: The last cell of a column does not necessarily mark the last record of a list. If the last record of the list has a missing value at that column position, you will overwrite existing records.

Spreadsheets have no concept of tables, records nor fields. Nothing prevents the storage of incomplete records in a supposed table which is nothing more than an arbitrary rectangle of cells. Nothing prevents that someone adds arbitrary data below the supposed table…Nothing enforces that a supposed record is valid or complete.
Just like obj.end(xlup), any navigation keystroke on a spreadsheet can spawn a big surprise, for instance when you hit Ctrl+End and the cell cursor jumps to some unkonwn area where someone typed in a memo or shopping list.

Spreadsheets, particularly databases on spreadsheets, in a medical environment is completely beyond quality assurance.

#6

Analog to last week’s function:

Function getLastContentIndex(iSheet As Long, iCol As Long) As Long
With com.sun.star.sheet.CellFlags
	iFlag = .VALUE + .STRING + .DATETIME + .FORMULA
End With
oSheet = ThisComponent.Sheets.getByIndex(iSheet)
oCol = oSheet.Columns.getByIndex(iCol)
oRanges = oCol.queryContentCells(iFLag)
n = oRanges.getCount()
oRg = oRanges.getByIndex(n-1)
getLastContentIndex = oRg.RangeAddress.EndRow
End Function
#7

And since we are in the business of spoon feeding:

from com.sun.star.sheet.CellFlags import VALUE, STRING, DATETIME, FORMULA
def getLastContentIndex(iSheet, iCol):
    iFlag = VALUE + STRING + DATETIME + FORMULA
    ThisComponent = XSCRIPTCONTEXT.getDocument()
    oSheet = ThisComponent.Sheets.getByIndex(iSheet)
    oCol = oSheet.Columns.getByIndex(iCol)
    oRanges = oCol.queryContentCells(iFLag)
    n = oRanges.getCount()
    oRg = oRanges.getByIndex(n-1)
    return oRg.RangeAddress.EndRow
#10

Where is the documentation for this? Which books you read?

#11

We (mostly) dont read books, we do LibreOffice - VBA - End(Xlup).offset(1,0) = Equivalaent in python - #2 by karolus

#13

Nobody has ever been fired for using Microsoft products, no matter how bad this decision was. If your medication turns out to be fatal due to an error in your LibreOffice spreadsheet and a misunderstood Python macro, you will be fired instantly.

#16

In my opinion, the analogs of the Range.End property are gotoStart and gotoEnd methods. Example:

Sub Test
  Dim oSheet, oCursor
  oSheet=ThisComponent.Sheets(0)
  oCursor = oSheet.CreateCursorByRange(oSheet.Columns(0))
  oCursor.gotoEnd
  Msgbox "Last filled cell in column A: "  & oCursor.AbsoluteName
End Sub
1 Like
#17

Way better than mine. It works with any other range or with the entire sheet instead of oSheet.Columns(0).

oCursor = oSheet.CreateCursorByRange(oSheet)

gives the bottom right corner of the used area not counting any formatting, comment or object (as far as I can tell from a quick test).

def lastCell(rg):
    oCursor = oSheet.createCursorByRange(rg)
    oCursor.gotoEnd()
    return oCursor

the range argument can be any row, column or rectangle of cells including a whole sheet. The return value is the cell itself. You get its position from cell.getCellAddress()

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.