Ask Your Question
0

Convert VBA to Calc macro

asked 2017-11-11 07:21:48 +0200

datum gravatar image

I have a fairly simple VBA script for Excel.

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub FixPlatforms()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    For Each sht In ActiveWorkbook.Worksheets
    For Each strKey In fndList.Keys()
        sht.Cells.Replace What:=strKey, Replacement:=fndList(strKey), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next strKey
    Next sht
End Sub

How can I get this script to work in Calc? I don't know very much about Excel or Calc. Thanks!

edit retag flag offensive close merge delete

Comments

What is your goal, and what is this code supposed to do? I don't know very much about Excel either; this is a LibreOffice site. But it looks to me like this could be done in Calc without a macro, using a lookup table.

Jim K gravatar imageJim K ( 2017-11-11 15:48:50 +0200 )edit

The script searches for any instances of for example "Xerox Alto" and replaces them with "ALTO". The Excel script I am actually using has a much longer list of replacements.

datum gravatar imagedatum ( 2017-11-11 22:39:15 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-11-11 16:08:47 +0200

sub MyFindReplace(strSearch, strReplace)
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args(7) as new com.sun.star.beans.PropertyValue
args(0).Name = "SearchItem.AllTables"
args(0).Value = true
args(1).Name = "SearchItem.SearchFiltered"
args(1).Value = false
args(2).Name = "SearchItem.Pattern"
args(2).Value = false
args(3).Name = "SearchItem.Content"
args(3).Value = false
args(4).Name = "SearchItem.AlgorithmType"
args(4).Value = 0
args(5).Name = "SearchItem.SearchString"
args(5).Value = strSearch
args(6).Name = "SearchItem.ReplaceString"
args(6).Value = strReplace
args(7).Name = "SearchItem.Command"
args(7).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args())

end sub

Sub FixPlatforms()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    For Each strKey In fndList.Keys()
        MyFindReplace strKey, fndList.Item(strKey)
    Next strKey
End Sub
edit flag offensive delete link more

Comments

Thanks! Could you also tell me how to limit this search to a particular column in a particular worksheet?

datum gravatar imagedatum ( 2017-11-11 23:31:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-11 07:21:48 +0200

Seen: 744 times

Last updated: Nov 11 '17