Can't open the Workbook

[Drifter]

I have a puzzle for you.
I have macros stored in an external reference workbook (for a number of reason that I will not bore you with).

To help this work, I have a function to test if the external workbook is open, and open it if it isn't.
this function is called 'openWBSRefWorkBook'.

Now, if i call this with a test sub, within the calling workbook (see attached), it works fine. that is, if the workbook isn't open, it opens it.

But, when I call it from through the functions it fails to open the workbook. Not only that, it doesn't throw any errors, it is like it doesn't even run the code line. I can copy the workbook.open statement to the immediates window and it works fine. If the the workbook is already open, the functions work fine.

Also, I am referencing a .xlsm workbook. But I've tested the code with .xlsx books as well.

i am baffled.

' 1
' the function
Function openWBSRefWorkBook(Optional scriptWB As Workbook) As Boolean
'  script assigns the arg to the external workbook,
  '  either already open or open it

Dim macroBookName, macroBookPath, fName
Dim RefWB As Workbook
  
  macroBookName = getWBSReferenceNames("wbName")
    '  this function call simply returns the book name as a string

'  already open
  On Error Resume Next
  Set RefWB = Workbooks(macroBookName)
  On Error GoTo 0
  
'  else open it
  If RefWB Is Nothing Then
  macroBookPath = getWBSReferenceNames("wbPath")  
  ' dido, but instead the Ref Wbk path

  fName = macroBookPath & "\" & macroBookName

  On Error GoTo 0 'Resume Next
  Set RefWB = Application.Workbooks.Open(Filename:=fName, ReadOnly:=True)
  On Error GoTo 0
  End If

'  finish and return
  If RefWB Is Nothing Then
  openWBSRefWorkBook = False
  Else
  '  assign to passed WB, if one was passed
  If Not scriptWB Is Nothing Then
  Set scriptWB = RefWB
  End If
  openWBSRefWorkBook = True
  End If
  
  
End Function

' 2
'  sub for testing the function
'  using htis will open the workbook
Sub example_OpenAndCloseScriptWorkbook()

Dim scriptWB As Workbook

  
'  reference the reference workbook
  '''gotRefWB = openWBSRefWorkBook(scriptWB)
  gotRefWB = openWBSRefWorkBook
  
  If Not (gotRefWB) Then
  MsgBox "failed to open the workbook!", vbCritical
  Exit Sub
  End If
  
'  Close the reference workbook
  CloseRefWB  ' function to close work book by name
  
End Sub

'  3
' production that will not open the work book (if it is closed)
Function exampleFunction (passedInArg As String) As String
' test if the WB will open

  Dim result As Boolean

'  ensure external workbook is open and build the call string
  Dim macroName As String, CallStr As String
  macroName = "theMacroToRun"
  CallStr = makeCallString(macroName)


'  exits at this if statement - due to the error I'm encountering
  If CallStr = "" Then
  GoTo errorExit
  End If




'  do
  result = Application.Run(CallStr, passedInArg)


'  exit and return values
  exampleFunction = result
  Exit Function
errorExit:
  exampleFunction = Null

End Function


'  4
Function makeCallString(macroName As String) As String

'  ensure that the reference workbook is open
  gotRefWB = openWBSRefWorkBook

'FAILS HERE, as gotRefWB returns 'False'
   If Not (gotRefWB) Then
     makeCallString = ""
     Exit Function
  End If


'  build the call string
  macroBookName = getWBSReferenceNames("wbName")
  makeCallString = macroBookName & "!" & macroName

End Function

 

[Marc L]

Process can't throw any error 'cause of On Error Resume Next
before Open statement ‼

Could use Dir function to verify if file exists on disk …

Three easy functions to test if a workbook is open !

From Workbooks collection :​

Function IsOpen(WBOOK) As Boolean
         On Error Resume Next
         IsOpen = IsObject(Workbooks(WBOOK))
End Function

From Windows collection :​

Function ExistWindow(WNAME) As Boolean
         On Error Resume Next
         ExistWindow = IsObject(Application.Windows(WNAME))
End Function

With ISREF worksheet function :​

Function ExistWorkbookSheet(WBOOK, WSHEET) As Boolean
                          V = Evaluate("ISREF('[" & WBOOK & "]" & WSHEET & "'!A1)")
         ExistWorkbookSheet = IIf(IsError(V), False, V)
End Function

Do you like it ? So thanks to click on bottom right Like !
​

 

[Drifter]

Thanks for the thoughts. I think I had commented the resume next out when I was testing.. If I run the function from the immediate window, it will open the workbook, so the issue isn't that the book cannot be found. When I use the secondary function to call it from a cell, like a formula, that is when it fails to open.

 

[Marc L]

Maybe less than 10 lines needed for this kind of code.

I never had an issue when I proceeded like I wrote in my previous post …​

 

[NARAYANK991]

Hi ,

Do you mean to say that when you use the function as a UDF , and try to use it in a cell formula , it does not work ?

Can you upload a sample workbook , with the relevant code , and the formula which you have used to call it from within the worksheet ?

Narayan