0

I would like to call function Ticker2Name from an excel cell. This function should open a file, give me the number of worksheets in the file, cleanup and close the file. The Set wbApproved never takes, but no error is triggered until I try to do Set ws = wbApproved.Sheets("Table 1") command, which gives me the error 91 "Object variable or with block variable not set". I am stumped. Here is the code.

Option Explicit
Function Ticker2Name(Ticker As String) As String
    'This file is C:\Test2\Sec.xlam and is references from the main file, Tr(RP).xlsm, so I may simple do
    '=Ticker2Name("ACEIX") in a C:\Test1\Tr(RP).xlsm cell.  The function should return ACEIX: 9 in the cell.
    
    Dim wbApproved As Workbook
    Dim ws As Worksheet
    Dim strApprovedFPath As String
    Dim strApprovedFName As String
    Dim wsCount As Integer

    On Error GoTo Done
    
    strApprovedFPath = Range("SB70Path") 'C:\Test\
    strApprovedFName = strApprovedFPath & Range("SB70Name") 'sb70.xlsx

    ActiveWindow.Visible = True
    Set wbApproved = Workbooks.Open(strApprovedFName)
    
    Set ws = wbApproved.Sheets("Table 1")
    
    ws.Activate
    wsCount = wbSB70.Worksheets.Count
    Ticker2Name = Ticker & ": " & wsCount
     Debug.Print wsCount
    
Done:
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Number & " - " & Err.Description
    End If
    Set ws = Nothing
    wbApproved.Close SaveChanges:=False
    Set wbApproved = Nothing

End Function

Thanks for your help. I appreciate what you do!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Rod
  • 151
  • 2
  • 4
  • 14
  • 1
    Related threads about opening a file with a UDF: [here](https://stackoverflow.com/questions/46782402/can-excel-vba-function-open-a-file), [here](https://stackoverflow.com/questions/23226657/excel-vba-load-worksheet-in-function/23232311#23232311), and probably others. – BigBen May 18 '21 at 20:33
  • 1
    `Activate` is most likely unnecessary, and you need `Option Explicit` to flag undeclared variables like `wbSB70`, unless that is a typo in your question. – BigBen May 18 '21 at 20:35
  • Thanks BigBen. Your comments answered the question. One more question: How do I mark it as answered? I Googled and did not see "the green tick-mark under the score of the answer" mentioned in https://www.wikihow.com/Ask-a-Question-on-Stack-Overflow – Rod May 18 '21 at 21:12
  • If one of those links helped, then your question may be a duplicate. Feel free to self-answer or just delete. – BigBen May 18 '21 at 21:22

0 Answers0