asked on

Return Excel.Workbook in VBA function

I have a function and I want to return an Excel.Workbook

However, I'm getting an object variable or with variable not set.

Any ideas?

Sub invAuto()
    
    Dim wbk As excel.Workbook
    'Getting Error here   
    wbk = openInvDoc
    
    wbk.Close False
    
End Sub

Function openInvDoc() As excel.Workbook
    Dim xlApp As excel.Application
    Dim wks As excel.Worksheet
    Dim wbk As excel.Workbook
    Dim path As String
    path = "C:\Weatherization\WX Inventory\1611 WX Materials Inventory\1611 WX Materials Inventory.xls"
    
    If Dir(path) = "" Then
        path = whereInvReport()
    End If
    
    If path = "" Then
        MsgBox "File not found or operation cancelled", vbInformation, "Operation Cancelled"
        Exit Function
    End If
   
    Set xlApp = CreateObject("excel.application")
    Set wbk = xlApp.Workbooks.Open(path)
    xlApp.Visible = True
    'Set wks = wbk.Worksheets("rptContractorSchedule")
    
    openInvDoc = wbk
        
End Function

Open in new window

ASKER CERTIFIED SOLUTION