Solved

ActiveVBProject not pointing to active VB Project

Posted on 2007-12-05
2
537 Views
Last Modified: 2011-09-20
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

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

Open in new window

0
Comment
Question by:PeterFrb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 20414239
thisworkbook relates to teh parent excel file for a specific macro therefore where you would call your function just use the literal thisworkbook.

Chris
0
 

Author Closing Comment

by:PeterFrb
ID: 31412936
Thank you!  I've been working on workarounds for this problem and did not realize how simple it is.  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question