Solved

Return Excel.Workbook in VBA function

Posted on 2011-03-08
3
497 Views
Last Modified: 2012-05-11
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
Comment
Question by:ClaudeWalker
3 Comments
 
LVL 4

Accepted Solution

by:
Limey1212 earned 250 total points
ID: 35071758
wbk = openInvDoc _ this line should read

set wbk = openInvDoc
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 35071786
Similarly in the function you need:
[b] Set[/b] openInvDoc = wbk

Open in new window

0
 

Author Comment

by:ClaudeWalker
ID: 35071789
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now