Get calling workbook path?

[Gustaf]

Hi all,

I have a solution where a set a data workbooks (with no VBA code) all referring (with Tools > References) to a code workbook (full of code). So when a data workbook opens, it calls the code workbook, and the Auto_Open() procedure in the code workbook is run.

I wonder if it's possible for Auto_Open() to know which file called the code workbook?

Gustaf

 

[Bernie Deitrick]

Gustaf,

If you have a naming convention, you could check all the open workbooks to see which workbook has
that type of name.

You could also check the references of the active workbook:

Sub TryNow()
Dim myRef As Reference
For Each myRef In ActiveWorkbook.VBProject.References
If myRef.Name = "CalledVBAProjName" Then
MsgBox "I'm being called by " & ActiveWorkbook.FullName
End If
Next myRef
End Sub

You could also cycle through the open workbooks if the activeworkbook is changed by the act of the
code workbook being opened (I would hide that workbook...)

HTH,
Bernie
MS Excel MVP

 

[Gustaf]

Thank you Bernie,

Your code is quite useful, even though it's not fully watertight for my purpose. There is a naming convention, but there may be several data workbooks open at the same time, all of them using the same prefix in the filename.

I'm aware of the ActiveWorkbook and ThisWorkbook objects in the API, and I wonder if maybe ActiveWorkbook would still point to the calling workbook in Auto_Open(). I haven't explicitly activated the code workbook after all. I will try this tomorrow, to see how it turns out.

Gustaf

--