Windows 11 23H2
LibreOffice 25.2.0.3 x86_64
I am (tryig to) write a macro, the intention of which is to copy the contents of column A, on Sheet1, to teh corresponding column on Sheet2
Beng a newbie, I’ve consulted docs, done searches etc, and come up with the following:
Blockquote
Sub CopySingleColumn()
Dim oDoc As Object, oSheetSource As Object, oSheetDest As Object
Dim oRangeSource As Object, oRangeDest As Object
Dim xRangeDest As Object
Dim i As Long, lastRow As Long
Dim oStartCell As Object
Dim colNameRegex As Object, matches As Object
oDoc = ThisComponent
’ *** VERY Robust Sheet Getting ***
oSheetSource = oDoc.Sheets.getByName(“Sheet1”) ’ Replace “Sheet1” with your source sheet name
If IsNull(oSheetSource) Then
MsgBox “Source sheet ‘Sheet1’ not found!”, vbCritical, “Copy Column”
Exit Sub
End If
oSheetDest = oDoc.Sheets.getByName(“Sheet2”) ’ Replace “Sheet2” with your destination sheet name
If IsNull(oSheetDest) Then
MsgBox “Destination sheet ‘Sheet2’ not found!”, vbCritical, “Copy Column”
Exit Sub
End If
’ *** VERY Robust Column Check ***
Dim sourceColumn As String
sourceColumn = “A” ’ Source Column
If sourceColumn = “” Then
MsgBox “Source column is not specified!”, vbCritical, “Copy Column”
Exit Sub
End If
’ Check if the column name is valid (A-Z, case-insensitive, one or more characters)
Set colNameRegex = CreateUnoService(“com.sun.star.util.Regexp”) ’ Create regex object
colNameRegex.Expression = “^[A-Za-z]+$” ’ Regular expression for one or more letters
colNameRegex.CaseSensitive = False ’ Case-insensitive matching
Set matches = colNameRegex.createSearchCursor(sourceColumn)
If matches.Count = 0 Then ’ No match found
MsgBox "Invalid source column name: " & sourceColumn, vbCritical, “Copy Column”
Exit Sub
End If
’ *** Last Row Calculation (with NULL check) ***
Dim oCursor As Object
oCursor = oSheetSource.createCursor()
If IsNull(oCursor) Then
MsgBox “Source sheet is empty.”, vbInformation, “Copy Column”
Exit Sub
End If
oCursor.gotoEndOfUsedArea(False)
lastRow = oCursor.RangeAddress.EndRow + 1
If lastRow = 0 Then Exit Sub
’ *** Range Getting (with error handling) ***
On Error GoTo RangeError ’ Set an error handler
oStartCell = oSheetSource.getCellRangeByName(sourceColumn & “1”) ’ Get the first cell in the column
oRangeSource = oStartCell.Resize(lastRow, 1) ’ Resize to the correct range
On Error GoTo 0 ’ Reset error handler
’ *** Destination Range (Create cell if needed) ***
Dim destStartCell As String
destStartCell = “A1” ’ Set destination start cell here (e.g., “B2”, “C5”, etc.)
oRangeDest = oSheetDest.getCellRangeByName(destStartCell)
oSheetDest.getCellByPosition(oRangeDest.CellAddress.Column, oRangeDest.CellAddress.Row).Value = “” ’ Force cell creation
xRangeDest = oRangeDest.queryInterface(com.sun.star.sheet.XCellRange)
xRangeDest = xRangeDest.Resize(oRangeSource.Rows.Count, 1)
xRangeDest.Value = oRangeSource.Value
Exit Sub ’ Important: Exit the macro normally
RangeError: ’ Error handler
MsgBox “Error getting source range. Check column and data.”, vbCritical, “Copy Column”
Exit Sub
End Sub
Blockquote
The problem is that, when I try to run the macro, I get teh folowing error:
BASIC runtime error.
Object variable not set.
The above is pointing to line 35, which reads:
colNameRegex.Expression = “^[A-Za-z]+$” ’ Regular expression for one or more letters
I’m probably doing somehng wrong or making things way to complicated!