How does this code open up another file?

[PipBoy808]

The workbook I'm using is Workbook Y - one of several workbooks within a given tool. The macro in question is designed to extract data from Workbook X into Workbook Y. The following is an error handler within that macro that is designed to physically open Workbook X if normal extraction of data into Workbook Y does not succeed. My question is, how does it accomplish this if the filepath of Workbook X is not explicitly mentioned?

    'Open Workbook X if necessary
    On Error Resume Next
    Set wbook = Workbooks(sHistFile)
    On Error GoTo 0
    IsOpen = False
    If wbook Is Nothing Then
        'open the file if not already open
        sHistFile = SafeOpenFile(ActiveWorkbook.Path & "\" & sHistFile)
 Else
        'make a note so we don't close Workbook X.
         IsOpen = True
    End If

The code calls upon the function 'SafeOpenFile', which is here:

Function SafeOpenFile(sFilePath As String)
'===============================================================
' Opens a file, and offers a dialog box to choose one you find yourelf if it can't find them
 
    Dim sFoundFile As String
    Dim z As Variant
   
    sFoundFile = Dir(sFilePath) 'Find file
   
    'Check that it finds a file, if not asks to specify
    If Len(sFoundFile) = 0 Then
        z = MsgBox("File " & sFilePath & " not found, do you want to manually specify?", vbYesNo)
        If z = vbYes Then
            sFoundFile = Application.GetOpenFilename("Stocklists (*.xls; *.xlsx; *.csv),*.xls,*.xlsx,*.csv", , "Specify the KB Stock List...")
            Workbooks.Open Filename:=sFoundFile, ReadOnly:=True 'Open it Read Only
            sFoundFile = Right(sFoundFile, Len(sFoundFile) - InStrRev(sFoundFile, "\")) 'trims the filepath off so we can use the name to reference it
        Else
            SafeOpenFile = "" 'Returns a zero length string on fail
            Exit Function
        End If
    Else
        Workbooks.Open Filename:=sFilePath, ReadOnly:=True 'Open it Read Only
    End If
   
    SafeOpenFile = sFoundFile 'Returns the name of the file to use
 
End Function

Again, SafeOpenFile does not specify the path of Workbook X. I think I'm missing something really obvious here, like I need to look at the definition of an additional function that contains the filepath. Can anyone identify something in the code that is present in place of a filepath? It might be the missing link.

I know this is a long post, so thanks a lot in advance! :D

 

[Sam Mathai Chacko]

It is opening the file from the same path as your originally opened workbook. ActiveWorkbook.Path does just that.

 

[PipBoy808]

Excellent, thanks. I had a feeling it would be something straightforward that I missed. However, 'sHistFile' is not the destination name of Workbook X so I'm still one step away from understanding this:

    If wbook Is Nothing Then
        'open the file if not already open
        sHistFile = SafeOpenFile(ActiveWorkbook.Path & "\" & sHistFile)

'sHistFile' is listed as:

 sHistFile = shtHistStock.Range("rngHistFile").Value

in another part of the macro. Is this referring to a specific cell or range that might contain the filename? Is there anyway to activate 'sHistfile' so that I can see what it's referring to?

 

[Sam Mathai Chacko]

Yes, you can add a line

MsgBox sHistFile

within that sub-routine somewhere to throw you a message with the content of that variable. Alternatively, you could use

Debug.Print sHistFile

and go to the immediate window in the vba editor by pressing CTRL+G

 

[Sam Mathai Chacko]

And yes, sHistFile is getting it's value from the range Range("rngHistFile") which is in sheet contained in the variable shtHistStock

 

[PipBoy808]

Fantastic. I can actually see the name of Workbook X within shtHistStock, which must be what 'rngHistFile' refers to. Weirdly, if I right-click on 'rngHistFile' on try to find the definition, it says it hasn't been defined. I get the message:

"Identifier under cursor is not recognised"

Is it possible that the original error handler is broken?