Excel/Microstation VBA conflict - Error 1004: PasteSpecial method of Range Class Failed

Hello,

I have developed an Excel spreadsheet which generates a list of hazards to be pasted into a MicroStation drawing as a linked Excel spreadsheet. Whenever a new list box is created, its formatting is incorrect and there is a macro, which rectifies it and adds the warning triangles to the box. The macro clears all shapes off the sheet (except for a template warning triangle) and then copies triangles wherever they are needed.

Whenever MicroStation is open and this macro is run, error ‘1004: PasteSpecial method of Range class failed’. The error happens:

  • Whether a file is loaded or not
  • Whether the file has been linked to the excel sheet or not
  • On multiple computers
  • Whether the PasteSpecial is defined to use explicitly the Excel Range object (rather than the MicroStation one

The error occurs in the following section:

' Iterate through all rows
For rowIndex = 11 To 1010
    ' Check if risk has a number
    If IsNumeric(ws.Cells(rowIndex, 2)) Then
        ' Insert image
        ws.Shapes("hazardTriangle").Copy
        ws.Range("B" & rowIndex).PasteSpecial
    End If
Next rowIndex

I am completely stumped as to why this error is occuring. The ws variable is defined as the active sheet in the active workbook, so there shouldn't be any confusion with MicroStation. If anyone has any idea how to resolve this problem, please do let me know.

Thanks!

Parents Reply Children