PeterFrb
asked on
ActiveVBProject not pointing to active VB Project
I am using code (see attached snippet) that I wish would give me the workbook connected to the active code module. However, there is a "hidden" code module that automatically comes attached with any VB program I write in Excel called "Insert Visio Button.XLS", which is obviously an addon from Visio. I say "hidden" because the Excel file is not visible from the Excel application, but it shows up in the Project Explorer window. What I'm finding is that the code below most often returns the workbook connected to code, but it sometimes returns the workbook connected to "Insert Visio Button.XLS", which, in my view, should never truly be the active code module.
If someone has a better solution to achieve the desired result, I'm listening.
Thank you, Peter Ferber
If someone has a better solution to achieve the desired result, I'm listening.
Thank you, Peter Ferber
Function WorkbookConnectedToCode(UseApp As Excel.Application) As Workbook
Set WorkbookConnectedToCode = UseApp.Workbooks(GetFullFileName(UseApp.VBE.ActiveVBProject.Filename))
End Function
Function GetFullFileName(strFullPath As String) As String
Dim iLastSlash As Integer
iLastSlash = InStrRev(strFullPath, "\")
GetFullFileName = Mid(strFullPath, iLastSlash + 1)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER