CODEWITHSUNDEEP
vbaexcelobjectcatia
ccan
ccan

Reputation: 13

VBA Excel GetObject Issue - Run Time Error 91 - Object Variable or With block Variable not Set

I am getting an error:

"Run-Time error 91: Object Variable or With block variable not set" .

Debugging highlights the line with MsgBox():

Sub CATMain()

    Dim xlApp As Excel.Application    
    Set xlApp = VBA.GetObject("", "Excel.Application")    
    Dim exlBook As Excel.Workbook
    Set exlBook = xlApp.ActiveWorkbook    
    MsgBox exlBook.Name

End Sub

What could be wrong?

Upvotes: 2

Views: 1277

Answers (3)

simpLE MAn
simpLE MAn

Reputation: 1622

In a CATIA vb module:

Sub CATMain()

    Dim xlApp As Object

    On Error Resume Next
    Set xlApp = VBA.GetObject(, "Excel.Application")
    On Error GoTo 0

    If (xlApp Is Nothing) Then
        MsgBox "Please start MS Excel prior running this script!!", vbCritical, "Excel not started"
        Exit Sub
    End If

    Dim exlBook As Object

    On Error Resume Next
    Set exlBook = xlApp.ActiveWorkbook
    On Error GoTo 0

    If (exlBook Is Nothing) Then
        MsgBox "No Workbook is openned", vbExclamation, "MS Excel is empty"
        Exit Sub
    End If

    MsgBox exlBook.Name

End Sub

Upvotes: 0

EvR
EvR

Reputation: 3498

Add:

 xlApp.Application.Visible = True

after set ...

You'll see why there's no activeworkbook.name

Because of the "" you open a new instance

Upvotes: 0

Vityata
Vityata

Reputation: 43593

Remove the "" from your code in VBA.GetObject() and it should work:

Sub TestMe()

    Dim xlApp As Excel.Application        
    Set xlApp = VBA.GetObject(, "Excel.Application")        
    Dim exlBook As Excel.Workbook
    Set exlBook = xlApp.ActiveWorkbook        
    MsgBox exlBook.Name

End Sub

MSDN GetObject. These are the parameters of GetObject():

  • pathname - Optional; Variant ( String ). The full path and name of the file containing the object to retrieve. If pathname is omitted, class is required.

  • class Optional; Variant ( String ). A string representing theclass of the object.

Upvotes: 2

Related Questions