none
Looking for a VBA macro to move modules into the global.mpt and delete it beforeclose event RRS feed

  • Question

  • Hi,

    I am looking forward to get help in achieving the following:

    When executing a particular project I need to move certain modules (Macros) into the Global.mpt when the open event takes place and remove the loaded modules from the global when the BeforeClose Event takes effect. The second one is to be able to upload the reference libraries based on the selection within the project to upload the reference Excel object library, word Object Library, Outlook library and/or PowerPoint Object Library using Office 2007.

    Looking forward to your help, please accept my best regards.

    Chuck

    Thursday, May 8, 2014 6:34 PM

Answers

All replies

  • You can do this using OrganizerMoveItem, but why do you need to move to Global?

    The references is a different issue. I would code and debug using references setup manually, but go live with no references. Research late binding code. This will use Objects rather than Word or Excel objects.

    So

    Dim xlApp as Object

    rather than

    Dim xlApp as Excel.Application


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, May 8, 2014 7:35 PM
    Moderator
  • Hi Rod,

    Thanks for your prompt reply. Answering your question regarding the fact of moving modules to the Global.mpt temporarily while one the project is running is to provide access to those module a group working on the particular project and therefore providing them access to the modules that refers to that project. I may be wrong, but I prefer to have the Global.mpt always clear from any modules and keeping it with its original module unless required.

    Correct me if I am wrong.

    On the other hand, I found one of your responses to people asking for almost similar request, the following command:

    OrganizerMoveItem Type=3, Filename:="My Macro Project", ToFileName:="Global.MPT", Name:="MyModule"
    OrganizerMoveItem Type:=6, Filename:="abc.mpp", ToFileName:="Global.MPT", Name:="abc"
    

    I am wondering what is the difference between Type=3 and Type=6

    Now referring to referencing to object I have successfully realized the referencing aspect but for some weird reason I get a message that pops-up as follow but when I click on Continue the process of removing the reference works.

    Public Sub addRefToOffice() On Error Resume Next Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE" Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\system32\scrrun.dll" End Sub Public Sub removeRefToOffice() Dim ref As Object On Error Resume Next ' Set ref = Application.VBE.ActiveVBProject.References("Office") Set ref = Application.VBE.ActiveVBProject.References("Excel") Application.VBE.ActiveVBProject.References.Remove ref '--- I did insert this loop to allow sufficient time for the process remove the reference

    'since I am using the same variable here "ref"

    For i = 1 To 500 Next i On Error Resume Next Set ref = Application.VBE.ActiveVBProject.References("Scripting") Application.VBE.ActiveVBProject.References.Remove ref End Sub

    But based on what you suggested one may use a different approach that may not require the referencing.

    I guess it is the Late bind instead of early bind If I remember. Bear with me Rod, I am a newbie in VBA and trying to apply my knowledge to make ease my task and the tasks of other around me.

    Appreciate your feedback.

    Regards,

    Chuck

    Thursday, May 8, 2014 11:32 PM
  • I forgot to insert the message I was getting only when I am in debug mode but work smoothly otherwise if I am not in debug mode.

    Thursday, May 8, 2014 11:38 PM
  • Some statements can't be stepped thru, only run. This is one of them. To debug at a breakpoint to the following statement then press F5 to run to it.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Saturday, May 10, 2014 12:15 AM
    Moderator
  • Thanks Rod, I was also expecting to get the explanation about the following:

    OrganizerMoveItem Type=3
    or
    OrganizerMoveItem Type=9

    By the way, I just ordered your book as I have realized that it has an excellent review.

    Cheers!Chuck

    • Edited by chamdan1 Saturday, May 10, 2014 12:59 AM Forgot one thing
    Saturday, May 10, 2014 12:57 AM
  • 3 is modules, 9 is fields. Full list in VBA help for OrganizerMoveItem

    Thank you for buying the book and happy programming! I normally use the pjModules and pjFields constants, but I think the code you showed was a copy of the original poster's code.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by chamdan1 Saturday, May 10, 2014 11:35 PM
    Saturday, May 10, 2014 11:03 PM
    Moderator
  • Thank you Rod and yes the codes above in my earlier question was effectively taken from the net, Can't remember who was it but I was curious to know about those type=3 and type=6. Thanks for explaining and providing me with tips. I am actually not a programmer but have actually appreciated coding in VBA as it provides so much facilities to achieve certain things I use in managing my projects.

    Thank you again Rod and have yourself a great weekend.

    Cheers!

    Chuck

    Saturday, May 10, 2014 11:41 PM