Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel connecting to MSProject (2000)

    Hi,

    I want to control MS Project from Excel, anybody go any tips on how to start this. What I am specifaclly looking for is:-

    Start MS Project from Excel
    Start MS Project Macro
    Enter data in MS Project macro input boxes
    Read MS Project tasks directly into Excel

    Any pointers gratefully received.

    _________________
    Many Thanks
    Mike

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel connecting to MSProject (2000)

    Well, the only system that I could cob together is XL97 & Proj4, but I think it will do.
    First, in Excel VBA, use Tools | References to set a reference to Microsoft Project. This will allow you to use all of the Project Object Model in Excel, but be careful, some objects may have the same name in Excel & Project, but they are different, so be sure to prefix all of the Dim's for Project objects with MSProject.
    Test out the following code:
    <pre>Option Explicit
    Sub p()
    Dim pjApp As Object

    Set pjApp = CreateObject("MSProject.Application")
    pjApp.Visible = True
    pjApp.FileOpen ("C:Flash.MPP")
    pjApp.Macro ("ViewGantt")
    MsgBox "Click OK to continue"
    pjApp.Quit pjDoNotSave
    Set pjApp = Nothing
    End Sub</pre>


    If later versions of Project have the Application object exposed, then you can change the Dim for pjApp to
    <pre>Dim pjApp as New MSProject.Application</pre>

    and get rid of the CreateObject line, however, Proj4 won't let me do this. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel connecting to MSProject (2000)

    Thanks Sammy, i'll give it a go......................

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel connecting to MSProject (2000)

    Hi Sammy,

    That worked fine thanks, I have just one extra question, how can I get data between Excel Macro and the linked MSProject macro. Currently I have some Inputbox's in Project asking questions which I need to reply to from Excel, passing parameters would be better. Then I need to read all the tasks in the project plan in Excel.

    Any ideas

    Regards

    Mike

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel connecting to MSProject (2000)

    You can access cells on your worksheet with either the Cells or the Range methods. Here's an example, where the start date for my first task is in B5. The MsgBox's are there just so that you can see the Start Date before and After. Note, you probably will want to move whatever code that you have in your MS Project macro into this Excel macro. No sense having to be in two places at once. HTH --Sam
    <pre>Option Explicit

    Sub p()
    Dim pjApp As Object
    Dim pjProj As MSProject.Project

    Set pjApp = CreateObject("MSProject.Application")
    pjApp.Visible = True
    pjApp.FileOpen ("C:Flash.MPP")
    pjApp.Macro ("ViewGantt")
    MsgBox "Click OK to continue"
    Set pjProj = pjApp.ActiveProject
    pjProj.Tasks(1).Start = Worksheets("Sheet1").Range("B5")
    MsgBox "Click OK to continue"
    pjApp.Quit pjDoNotSave
    Set pjProj = Nothing
    Set pjApp = Nothing
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel connecting to MSProject (2000)

    Thanks Sam

    That is really helpfully, I have also discovered that I can impend all my MSProject macro commands within Excel so infact I do not need MSProject macros at all now.

    Thankyou very much for your help

    Regards

    Mike

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel connecting to MSProject (2000)

    Exactly, that's what setting a reference to MS Project did for you. Two worlds at your fingertips(i.e. both Excel & Project in your auto-complete, in your object browser and in your help files). Remember to explicitly define every variable and to prefix every dim of a Project object with MSProject. There are objects in both worlds with the same name, for example, Project's Pane object is different from Excel's Pane. If you don't prefix it, the dim defaults to the Excel object. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •