Convert VBA to LibreOffice Basic

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
13taha33
Posts: 3
Joined: Thu Jul 06, 2017 8:49 pm

Convert VBA to LibreOffice Basic

Post by 13taha33 »

Hello,

I have received a macro from another forum that appears to do exactly what I require, though I neglected to mention I needed it in Basic and not VBA, so I am requesting someone to take a look at it and convert it to the type that I need to work with LibreOffice Calc. If anyone can do this for me, I would be extraordinarily grateful.

Thank you.

Code: Select all

Sub ArtLookup()
Dim sValue, rValueAddr As String, rValue As Range, lRow52 As Long, ws As Worksheet

On Error Resume Next

sValue = InputBox("Enter the value you want to search for:", "Search Value?")

If sValue = vbNullString Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
    With ws.UsedRange
        Set rValue = .Cells.Find(What:=sValue, After:=.Range("A1"), _
                                    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, MatchCase:=False)
                                    
        If Not rValue Is Nothing Then
            rValueAddr = rValue.Address
            
            Do
                Set rValue = .FindNext(rValue)
                If Worksheets(Sheets.Count).Range("G1").Value = "" Then
                    lRow52 = 0
                Else
                    lRow52 = Worksheets(Sheets.Count).Cells(Rows.Count, "G").End(xlUp).Row
                End If
                
                Worksheets(Sheets.Count).Range("G" & lRow52 + 1).Value = rValue.Offset(, -1).Value
            Loop While Not rValue Is Nothing And rValue.Address <> rValueAddr
        End If
    End With
    Set rValue = Nothing
Next
End Sub
LibreOffice 5.3.3.2 with MacOS 10.11.06
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to LibreOffice Basic

Post by Zizi64 »

Your VBA macro can run for me in my LO 4.4.7 with the

Code: Select all

option vbasupport 1
option value at the beginning of the code.

But it is better to rewrite all of your VBA macros based on one of the supported programming languages (for example: the StarBasic) and the Application Programming interface functions of the LibreOffice (or the Apache OpenOffice)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
KingTamo
Posts: 4
Joined: Sun Nov 19, 2017 7:49 am

Re: Convert VBA to LibreOffice Basic

Post by KingTamo »

Thanks a lot for reply
Forgive me as I am totally newbie

I have enabled macros in OpenOffice and when trying to execute I encountered red error part for the string in this line

Code: Select all

Open "YourTextFile.txt" For Input As #fi
How can I put the quotation for strings?
OpenOffice 3.1 on Windows Vista
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Convert VBA to LibreOffice Basic

Post by Lupp »

KingTamo wrote:How can I put the quotation for strings?
Sorry! Don't understand what you want.
Are you aware of the fact that the position of the statement filled with "YourTextFile.txt" in your example must be an absolute filename in your file system or, even better the respective URL. In addition the file must exist, of course, to be opened for input.

If you want to work with files contained in the same folder as the component calling the Sub you can use the applicable parts of this example:

Code: Select all

Sub readLinesFromTextFileInTheFolderOfThisComponent(Optional pName As String)
'Optional clause only for testing
If IsMissing(pName) Then pName="Source.txt" 'Only for testing, of course
tD=ThisComponent
Dim tURL As New com.sun.star.util.URL
usURL=CreateUnoService("com.sun.star.util.URLTransformer")
tURL.Complete=tD.URL
usURL.parseStrict(tURL)
fileURL=ConvertToURL(tURL.Path & pName)
If NOT FileExists(fileURL) Then 
 print "File not found!"
 Exit Sub
End If
fileNum=FreeFile
Open fileURL For Input As #fileNum
Do While NOT EOF(#fileNum)
 Line Input #fileNum, oneLine
 Print oneLine
Loop 
Close #FileNum
End Sub
By the way (for those interested): On a Win System the parsed tURL.Path has an additional slash in front of the drive. Leaving this slash there in the concatenated filename without a subsequent ConVertToURL() is accepted by the function FileExists() but causes an error with the Open command. A little strange inconsistency.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply