Convert VBA to LibreOffice Basic

Keyboard macros or custom scripts

Convert VBA to LibreOffice Basic

Postby 13taha33 » Thu Jul 06, 2017 9:23 pm

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   Expand viewCollapse view
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
13taha33
 
Posts: 3
Joined: Thu Jul 06, 2017 8:49 pm

Re: Convert VBA to LibreOffice Basic

Postby Zizi64 » Thu Jul 06, 2017 10:32 pm

Your VBA macro can run for me in my LO 4.4.7 with the
Code: Select all   Expand viewCollapse view
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; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.3 and AOO4.1.4
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: 6083
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to LibreOffice Basic

Postby KingTamo » Sun Nov 19, 2017 1:50 pm

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   Expand viewCollapse view
Open "YourTextFile.txt" For Input As #fi


How can I put the quotation for strings?
OpenOffice 3.1 on Windows Vista
KingTamo
 
Posts: 4
Joined: Sun Nov 19, 2017 7:49 am

Re: Convert VBA to LibreOffice Basic

Postby Lupp » Mon Nov 20, 2017 2:19 am

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   Expand viewCollapse view
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 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1523
Joined: Sat May 31, 2014 7:05 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests