• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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

0
ClaudeWalker
Asked:
ClaudeWalker
2 Solutions
 
Limey1212Commented:
wbk = openInvDoc _ this line should read

set wbk = openInvDoc
0
 
Rory ArchibaldCommented:
Similarly in the function you need:
[b] Set[/b] openInvDoc = wbk

Open in new window

0
 
ClaudeWalkerAuthor Commented:
Nice!

It turns out I need to set the caller and the return value

'caller
Set wbk = openInvDoc


'return
Set openInvDoc = wbk

Thanks,
JOe K.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now