Convert macro VBA in OpenOffice

Creating a macro - Writing a Script - Using the API

Convert macro VBA in OpenOffice

Postby eugenefoxx » Sat Apr 01, 2017 7:54 am

Hello!
How to change the next VBA script to the OpenOffice macro -

Code: Select all   Expand viewCollapse view
On Error Resume Next
    ThisWorkbook.Worksheets("Sheet2").Activate   ' i know is it oSheet = thisComponent.Sheets (1) in OpenOffice
    With ThisWorkbook.Sheets("Sheet2")   
        iLastrow = .Cells(Rows.Count, 5).End(xlUp).Row  ' What are the analogs of this method?
        a = Range(.[e3], .Range("E" & iLastrow)).Value
    End With
    With ThisWorkbook.Sheets("Sheet1")   
        iLastrow = .Cells(Rows.Count, 2).End(xlUp).Row
        b = Range(.[j6], .Range("B" & iLastrow)).Value
    End With
   
    ReDim c(1 To UBound(a), 1 To 7)
   
    With CreateObject("Scripting.Dictionary")
   
    For i = 1 To UBound(b)
            .Item(b(i, 1)) = i
    Next
   
    For i = 1 To UBound(a)
            If .exists(a(i, 1)) Then
                c(i, 1) = b(.Item(a(i, 1)), 5)
                c(i, 3) = b(.Item(a(i, 1)), 6)
                c(i, 4) = b(.Item(a(i, 1)), 8)
                c(i, 5) = b(.Item(a(i, 1)), 4)
            End If
    Next
    End With
       
    With ThisWorkbook.Sheets("Sheet2")   
        .[f3].Resize(UBound(c), 5) = c
        .Activate
    End With
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 27
Joined: Thu Jun 04, 2015 11:59 pm

Re: Convert macro VBA in OpenOffice

Postby Zizi64 » Sat Apr 01, 2017 8:07 am

Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6008
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert macro VBA in OpenOffice

Postby eugenefoxx » Sat Apr 01, 2017 4:27 pm

The problem with arrays. There are two sheets to the book. On the first page, we take column A and substitute the value in column B from the second page of the range A: B (search for the identification number by A with substitution of the description from B). As the formula VLOOKUP. Since there is a lot of information, I want to solve the problem through arrays.
LibreOffice 4.4.0.3 on Windows 8.1
eugenefoxx
 
Posts: 27
Joined: Thu Jun 04, 2015 11:59 pm

Re: Convert macro VBA in OpenOffice

Postby Villeroy » Sat Apr 01, 2017 4:34 pm

Drop all that macro madness and use VLOOKUP. Nothing is more efficient thatn the built-in functions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests