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

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" wrote in message ...
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

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

--
Bernie Deitrick wrote:
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" wrote in message ...
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