none
Automatically load the Excel 12.0 object library in VBA code? RRS feed

  • Question

  • Hi there.  I wrote a macro that exports filtered Projects task data to an Excel spreadsheet.  The macro itself works great but the users have to reload the Excel 12.0 object library everytime they use my macro on a new project file.  Does anyone know if a subroutine that I could use to check if that Excel Object library is loaded in MS Projects and if not the code would automatically load it?  Thank you for any assistance you could provide. 
    Wednesday, September 14, 2011 4:31 PM

All replies

  • Coconutdisco,

    Once a user has set the reference to the Excel Object library on their PC it should be valid for any macro or Project file they open thereafter. If that is not happening for your users, try having them open your export macro in the VB Editor, make sure the reference is set, and then go to File/Save Global.

    At one time (in the distant past), I recall writing code to actually set the reference and I think it required knowing the GUID value of Excel (from the registry), but I couldn't find that snippet of code. However, this is some code I use to check for the Excel object library reference:

     

    'this routine insures a reference is set for the Excel object library

    Sub RefChk()

    Dim XLRef As Boolean

    Dim oRef As Object

    XLRef = False

    For Each oRef In ThisProject.VBProject.References

        If oRef.Name = "Excel" Then

            XLRef = True

            Exit For

        End If

    Next

    If XLRef = False Then

        MsgBox "In order for the report to be properly formatted," & vbCr & vbLf _

            & "a reference to the Excel object library must be set." & vbCr & vbLf & vbCr & vbLf _

            & "If you are unsure how to do this, contact the author or" & vbCr & vbLf _

            & "your IT department for assistance.", vbCritical, MsgBxTitle & " - Fatal Error"

        End

    End If

    End Sub

    Hope this helps.

    John

     

    Thursday, September 15, 2011 2:46 AM
  • This will add a reference to Excel 14 from Project:

    ActiveProject.VBProject.References.AddFromGuid Guid:="{00020813-0000-0000-C000-000000000046}", major:=1, Minor:=7

    Minor:=6 is Excel 2007 and so on down.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, September 15, 2011 10:30 AM
    Moderator
  • Rod,

    Thanks for the tip on what "minor" represents. I remember way back when I used this syntax I tried to find out what "major" and "minor" represented. I never could find out. Where can I find a good definition of those parameters?

    John

    Thursday, September 15, 2011 3:57 PM
  • Thanks John!  This gets me halfway there.

    • Edited by Coconutdisco Thursday, September 15, 2011 6:15 PM
    Thursday, September 15, 2011 6:14 PM
  • Thanks  Rod,

    I am trying to combine John's answer with yours and I keep getting a compile error.

     

    Sub RefChk()

    Dim XLRef As Boolean
    Dim oRef As Object
    Dim GUID As String
    Dim Major As Long
    Dim Minor As Long

    XLRef = False

    For Each oRef In ThisProject.VBProject.References

        If oRef.Name = "Excel" Then

            XLRef = True

            Exit For

        End If

    Next

    If XLRef = False Then

        ActiveProject.VBProject.References.AddFromGuid GUID:="{00020813-0000-0000-C000-000000000046}", Major:=1, Minor:=6

        End

    End If

    End Sub

    I get a Run-timer error '32813'

    Name conflicted with existing module, project or project library.

     

    Any ideas?  Thanks again!

     


    • Edited by Coconutdisco Thursday, September 15, 2011 6:17 PM
    Thursday, September 15, 2011 6:17 PM
  • Coconutdisco,

    Your combined macro works fine for me. I tried it both with Excel 2003 and Excel 2007. I first cleared the reference which was already set for Excel and then ran the macro. In both cases it set the reference as desired. If the reference is already set, the code fell through as expected.

    On what line exactly do you get the error? From the VB Editor go to Tools/References. Is there more than one version of the Excel object library in the list?

    By the way. in an attempt to find my own answer to the question I asked Rod about the major and minor parameters, I googled the GUID property. I found this webpage which is most helpful. http://msdn.microsoft.com/en-us/library/aa159961(v=office.10).aspx

    John

    Thursday, September 15, 2011 11:46 PM
  • I don't think there is one. The values are set by teh Excel programmers and are unique for eavh version. You just have to read what they are.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Friday, September 16, 2011 10:05 AM
    Moderator
  • Hi John and Rod,

    I get the error on:

      ActiveProject.VBProject.References.AddFromGuid GUID:="{00020813-0000-0000-C000-000000000046}", Major:=1, Minor:=6

    The only Excel library in there is excel 12.0.  Any ideas?  Thank you both so much for the help!

     

    Matt (coconutdisco)

    Friday, September 16, 2011 12:35 PM
  • Matt,

    Unfortunately the message associated with an error is most often to cryptic to be of any use to the casual user, or even to those of us who have worked with VBA for many years. But let's go with what the error message does say and see if we can ferret out an issue. Some of these suggestions may seem dramatic but they are designed to insure backup is preserved.

    First, a simple approach. Try creating a new blank project file and copying your code to a new module attached to the file. Also change the name of the macro (e.g. instead of "RefChk", try something else).

    Add the following statement immediately before the ActiveProject.VBProject.Ref. . .

    On Error Resume Next

    Now put a breakpoint on the "On Error . . ." statement. Run the macro. When it hits the breakpoint use F8 to manually step through to the next statement. Open the Immediate window. Type "Print err.source" with a return. What do you get? Now type "Print err.description" with a return. What do you get? The description should be the message you saw originally.

    To cover some more bases, what are the active references when you go to Tools/References?

    Do you have any other macros in the subject file or in your Global? Do they use a reference to the Excel object library? If so, try deleting those macros. Note: you can use the Organizer to transfer those macros to a shell (blank) project file for later reinstating or you can save your whole Global to a separate location and go with a regenerated default Global.

    Hey, I'm just reaching here but its obviously more difficult to troubleshoot when we can't duplicate the problem. Maybe Rod or Jan, if he joins the thread, will have some other suggestions.

    John

    Friday, September 16, 2011 4:09 PM
  • Hi John,

    Interesting!  It looks like it depends where I run it from.  If I run it from the Global.mpt it does not work, I get the same error and the error print repeats the same error message. 

    If I copy it to VBA Project (local project) it works just fine.  I will have to play with it some more.  Thanks again for all of your help!!

    Cheers,

    Matt

    Friday, September 16, 2011 7:41 PM
  • Matt,

    Interesting. When I try it from my Global I too get the error. An attempt at Print err.Source yielded nothing. Then I found something interesting. If I look at the list of object libraries in the Global, Excel is listed twice, once for Excel 12.0 (the one you are trying to set) and one for Excel 5.0 (down in the other Microsoft libraries). I have no idea why it is there but I believe Excel 5.0 is for Office 2003 (or possibly earlier). Somehow it got carried forward in my Global.

    If I look at the list of object libraries from a project file (i.e. not the Global), I only see the one Excel library, namely Excel 12.0.

    So then I tried deleting the Excel 5.0 object library. The error message didn't appear when I ran the macro but it was replaced by something just as bad - I got a fatal Project error (i.e. Project crashed). Maybe the registry needs to be tweaked. I dunno.

    I may play with this more, but for now I think you safest bet is to run the code from a file, not from your Global.

    John

    Friday, September 16, 2011 9:08 PM