Libre office calc, used range

Fabrice Lambert
Fabrice Lambert used Ask the Experts™
on
Hi,

I have a LibreOffice Calcs document, and with VBA, from a workshet, I would like to:
- Retrieve the used range of the worksheet.
- Iterate over cells in that used range.

Here is what I have done so far:
Dim openOffice As Object
Set openOffice = CreateObject("com.sun.star.ServiceManager")

Dim starDesktop As Object
Set starDesktop = openOffice.createInstance("com.sun.star.frame.Desktop")

Dim args(0) As Object
Set args(0) = openOffice.Bridge_Construct("com.sun.star.beans.PropertyValue")
args(0).name = "Hidden"
args(0).value = True

Dim doc As Object
Set doc = starDesktop.loadComponent.FromUrl("file://c:/temp/myDoc.ods", "_blank", 0, args)

Dim ws As Object
ws = doc.sheets().getByName("Sheet1")

    '// used range ??????????

Open in new window


NOTES:
The used range can be anywhere in the worksheet (doesn't necessary start with A1 cell).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consulting
Distinguished Expert 2017
Commented:
After many trials and errors, I found a solution:
Dim openOffice As Object
Set openOffice = CreateObject("com.sun.star.ServiceManager")

Dim starDesktop As Object
Set starDesktop = openOffice.createInstance("com.sun.star.frame.Desktop")

Dim args(0) As Object
Set args(0) = openOffice.Bridge_Construct("com.sun.star.beans.PropertyValue")
args(0).name = "Hidden"
args(0).value = True

Dim doc As Object
Set doc = starDesktop.loadComponent.FromUrl("file://c:/temp/myDoc.ods", "_blank", 0, args)

Dim ws As Object
ws = doc.sheets().getByName("Sheet1")

Dim cs As Object
Set cs = ws.createCursor
cs.gotoStartOfUsedArea False

Dim startColumn As Long
startColumn = cs.getRangeAddress().startColumn

Dim startRow As Long
startRow = cs.getRangeAddress().startRow

cs.gotoEndOfUsedArea False

Dim endColumn As Long
endColumn = cs.getRangeAddress().endColumn

Dim endRow As Long
endRow = cs.getRangeAddress().endRow

Dim rng As Object
Set rng = ws.getCellRangeByPosition(startColumn, startRow, endColumn, endRow)

Dim i As Long
Dim j As Long
For i = 0 To rng.rows.count - 1
    For j = 0 To rng.columns.count - 1
        Dim cell As Object
        Set cell = rng.getCellByPosition(j, i)
            '//
            '// code interracting with cell object
            '//
        Set cell = Nothing
    Next
Next

Set rng = Nothing
Set cs = Nothing
Set ws = Nothing
doc.close False
Set doc = Nothing
Set args(0) = Nothing
startDesktop.terminate
Set starDesktop = Nothing
Set OpenOffice = Nothing

Open in new window

Fabrice LambertConsulting
Distinguished Expert 2017

Author

Commented:
Solution found.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial