Code: Select all
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
Could anyone tell me what went wrong there?
Cross-posted at: https://ask.libreoffice.org/en/question ... e-not-set/