[Solved] PasteSpecial runtime error in macro from Excel

[Randy134]

This vba code works in Excel (2003) but not in openoffice.
Openoffice does not like this...

Code: Select all

    ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False

It fails with...
Basic runtime error.
'423'
PasteSpecial

How do I fix this?
Thanks.

Code: Select all

Function NextToken(sht As String, backrow As Single, backrow_Scotia As Single) As String
'   On Error GoTo err1:
  
   Sheets(sht).Select
   
   Dim s1 As String
   Dim K1 As String
   Dim R1 As String
   Dim rn As Integer
   Dim br As Integer
   
'   br = backrow
'  If (sht = "Scotia") Then
'     br = backrow_Scotia
'  End If
  
    Set r = Range("B1")
    company = Worksheets(sht).Cells(2, 2)
    
' New way of finding end of rows...with no limit...

 Worksheets(sht).Activate
 Range("A589").Select
 ActiveCell.Value = "Monthly Totals"
 ActiveCell.Offset(0, 1).Activate
 
 Worksheets(sht).Activate
  Range("A1:D10").Select
 ActiveCell.CurrentRegion.Select
     LastRow = ActiveCell.CurrentRegion.Rows.Count



'    LastRow = ActiveSheet.UsedRange.Rows.Count
'    numrows = LastRow - br    'was backrow
    numrows = LastRow    'was backrow
    
   Do

    Set c1 = ActiveSheet.Rows(numrows)
    c1.Select
'    ActiveCell.Offset(0, 0).Rows("1:7").EntireRow.Select
'    If (ActiveCell.Offset(0, 0).Rows("0:" & numrows + 1).EntireRow.Select = "Insert above here") Then
    If (ActiveCell.Value = "Insert above here") Then
       Exit Do
    End If
       numrows = numrows - 1
   Loop
   numrows = numrows - 1
   br = LastRow - numrows
    Set c1 = ActiveSheet.Rows(numrows)
    c1.Select
'    ActiveCell.Offset(0, 0).Rows("1:7").EntireRow.Select
    ActiveCell.Offset(0, 0).Rows("1:" & br + 1).EntireRow.Select
    Selection.Copy
'    ActiveCell.Offset(1, 0).Rows("1:7").EntireRow.Select
    ActiveCell.Offset(1, 0).Rows("1:" & br + 1).EntireRow.Select
    ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    cp3 = numrows + 1
    Set c1 = ActiveSheet.Rows(cp3)
    c1.Select
    Set c1 = ActiveCell.Offset(0, 0)
    c1.Select
    c2 = Now()
    ActiveCell = c2
    Selection.NumberFormat = "mmm dd/yy ddd at hh:mm"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
  ActiveWindow.SmallScroll Down:=1
  NextToken = cp3
    Exit Function

[FJCC]

The Application Programming Interface of OpenOffice is completely different from the API of Excel. You can't just use VBA code in OpenOffice. Never having used VBA, I can't tell what your code does. If you are willing to learn the OpenOffice API, please explain what you are trying to do and people here can give you some advice, but you may have to put in a lot of work for a while.

[JeJe]

Have you put

Code: Select all

Option VBASupport 1
Option Compatible

At the top of your module?
Compatibility is people say better in LibreOffice - but you'll likely need to do rewriting regardless.
It'll help people help you better if you provide all the relevant code so people can run it and see where it fails, not an incomplete function without a sample call.

[Randy134]

Thanks for all the suggestions!
With a lot of cursing and swearing (not really!) I ended up rewriting most of the excel macro with a LOT of help from MS Copilot, grok, and the internet.
I now have a working OpenOffice macro!
And I may take a look at Libreoffice like was suggested...
Heh... being retired, I have time to do this kind of thing!