Object variable not set

Keyboard macros or custom scripts

Object variable not set

Postby vkhu » Thu Oct 01, 2020 1:04 pm

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:

Code: Select all   Expand viewCollapse view
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.

Could anyone tell me what went wrong there?

Cross-posted at: https://ask.libreoffice.org/en/question ... e-not-set/
Last edited by vkhu on Thu Oct 01, 2020 2:04 pm, edited 1 time in total.
OpenOffice 3.1 on Windows 10
vkhu
 
Posts: 2
Joined: Thu Oct 01, 2020 1:00 pm

Re: Object variable not set

Postby robleyd » Thu Oct 01, 2020 2:01 pm

Same question asked at https://ask.libreoffice.org/en/question ... e-not-set/ If you cross post, please note that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3947
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Object variable not set

Postby vkhu » Thu Oct 01, 2020 2:04 pm

robleyd wrote:Same question asked at https://ask.libreoffice.org/en/question ... e-not-set/ If you cross post, please note that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

Thanks for the head up. I've updated the post
OpenOffice 3.1 on Windows 10
vkhu
 
Posts: 2
Joined: Thu Oct 01, 2020 1:00 pm

Re: Object variable not set

Postby Lupp » Thu Oct 01, 2020 10:30 pm

It's really hard to help somebody with a macro as long as not the goal is clearly explained.
Someone wanting to help, but not knowing the ways it's done for a different application, may fail to understand from the code what should be achieved, while he might easily be able to solve the original problem.
When I tried now and then to help move VBA macros to LibreOffice (or AOO) a few years ago, I had cases where I spent hours, but just found out finally that the miraculous Excel-VBA-code just was banality in disguise, and that the actual task was solved with a few simple formulas.
That's why you don't get me easily again for such a task.
Describe your problem on the user level.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3054
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests