I’m trying to port a VBA macro from word over to libreoffice writer (“port” being used very loosely here, since I’m functionally illiterate in both languages). Here’s what I got:
Sub BulkReplaceWithExcel
Dim oDescriptor REM The search descriptor
Dim oDoc REM The shortened name for the document
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim bStrt As Boolean, iDataRow As Long, bFound As Boolean
Dim xlFList As String, xlRList As String, i As Long, Rslt
oDoc = ThisComponent
REM ThisComponent is the document, but we named it oDoc because oDoc is shorter
oDescriptor = oDoc.createReplaceDescriptor()
REM The search command for oDoc (the entire document)
StrWkBkNm = "C:\Users\Long\Dropbox\WIP word documents\6) Wildcards (dependent).xlsx"
StrWkSht = "Sheet1"
If Dir(StrWkBkNm) = "" Then
MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
Exit Sub
End If
REM Test whether Excel is already running.
On Error Resume Next
bStrt = False REM Flag to record if we start Excel, so we can close it later.
Set xlApp = GetObject(, "Excel.Application")
REM Start Excel if it isn't running
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
REM Record that we've started Excel.
bStrt = True
End If
On Error GoTo 0
REM Check if the workbook is open.
bFound = False
With xlApp
With xlWkBk.Worksheets(StrWkSht)
REM Find the last-used row in column A.
REM Add 1 to get the next row for data-entry.
iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row REM -4162 = xlUp
REM Output the captured data.
For i = 1 To iDataRow
REM Skip over empty fields to preserve the underlying cell contents.
If Trim(.Range("A" & i)) <> vbNullString Then
xlFList = xlFList & "|" & Trim(.Range("A" & i))
xlRList = xlRList & "|" & Trim(.Range("B" & i))
End If
Next
End With
If bFound = False Then xlWkBk.Close False
If bStrt = True Then .Quit
End With
REM Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
REM Process each word from the F/R List
For i = 1 To UBound(Split(xlFList, "|"))
With oDescriptor
.SearchString = Split(xlFList, "|")(i)
.ReplaceString = Split(xlRList, "|")(i)
.SearchRegularExpression=True
.searchAll=True
End With
REM Specify what to search for, what to replace with, and the type of search
oDoc.ReplaceAll(oDescriptor)
REM replace everything in oDoc with the command specified in oDescriptor
Next
End Sub
When I ran the code, I got an error at the line iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ’ -4162 = xlUp, saying “Object variable not set”.
As far as I can tell, iDataRow is the only nonstandard object in that line that needed to be clarified, and it was already clarified at the start. So what went wrong here?
Cross-posted at: Object variable not set (View topic) • Apache OpenOffice Community Forum