Announcement

Collapse
No announcement yet.

Import Data from MS Project 2003

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Import Data from MS Project 2003

    I would like to write a macro which imports certain data from a MS Project 2003 file. I found a Project macro which exports to excel but nothing on importing through excel. The main thing is how to get the project file opened.

    Thanks in advance!

    -Canuck

  • #2
    Re: Import Data from MS Project 2003

    Canuck

    I'm a little confused.

    Where are you importing/exporting to/from?
    Boo!

    Comment


    • #3
      Re: Import Data from MS Project 2003

      I want the macro to run from excel and import data from a project 2003 file. The reason I need the macro in excel is that the project file is a master schedule that is read-only. The code I've started with goes the other way and should be reversible. I really need to figure out the vba procedure to open and read from the project file.

      Code I'm working with:

      http://masamiki.com/project/export-h...-to-excel.html

      Comment


      • #4
        Re: Import Data from MS Project 2003

        Sorry I'm still confused.

        The code in that link is importing from Project to Excel.

        How is it not working for you?
        Boo!

        Comment


        • #5
          Re: Import Data from MS Project 2003

          Well I seem to have reversed the code and produced output however I still have a couple questions. Is there any way to open the file without actually displaying the project file on the screen. The problem is that this Project file is linked with several other files so when it opens it asks you whether you want to "open resource pools to see assignments across all sharer files". Eventually I want this macro to run on its own so I need to eliminate this user dependent behavior.

          Code:
          Option Explicit
          Dim xlRow As Excel.Range
          Dim xlCol As Excel.Range
          
          Sub TaskHierarchy()
          Dim xlApp As Excel.Application
          Dim xlBook As Excel.Workbook
          Dim xlSheet As Excel.Worksheet
          Dim t As Task
          Dim Asgn As Assignment
          Dim ColumnCount As Integer
          Dim Columns As Integer
          Dim Tcount As Integer
          Dim Proj As MSProject.Application
          
          Set Proj = CreateObject("MSProject.Application")
          Proj.Visible = True
          Proj.FileOpen Name:="\\frxfileserv2\Engrng\ProductEngineering\PV&V\Schedules\DT\DT Master Schedule.mpp", ReadOnly:=True
          
          
          Set xlApp = ThisWorkbook.Application
          'Set xlApp = New Excel.Application
          xlApp.Visible = True
          AppActivate "Microsoft Excel"
          
          Set xlBook = xlApp.Workbooks.Add
          Set xlSheet = xlBook.Worksheets.Add
          
          
          xlSheet.Name = Proj.Name
          AppActivate "Microsoft Project"
          'count columns needed
          ColumnCount = 0
          For Each t In ActiveProject.Tasks
              If Not t Is Nothing Then
                  If t.OutlineLevel > ColumnCount Then
                      ColumnCount = t.OutlineLevel
                  End If
              End If
          Next t
          
          'Set Range to write to first cell
          Set xlRow = xlApp.ActiveCell
          xlRow = "Filename: " & Proj.Name
          dwn 1
          xlRow = "OutlineLevel"
          dwn 1
          
          'label Columns
          For Columns = 1 To (ColumnCount + 1)
              Set xlCol = xlRow.Offset(0, Columns - 1)
              xlCol = Columns - 1
          Next Columns
          rgt 2
          xlCol = "Resource Name"
          rgt 1
          xlCol = "work"
          rgt 1
          xlCol = "actual work"
          Tcount = 0
          For Each t In ActiveProject.Tasks
              If Not t Is Nothing Then
                  dwn 1
                  Set xlCol = xlRow.Offset(0, t.OutlineLevel)
                  xlCol = t.Name
                      If t.Summary Then
                          xlCol.Font.Bold = True
                      End If
                  For Each Asgn In t.Assignments
                      dwn 1
                      Set xlCol = xlRow.Offset(0, Columns)
                      xlCol = Asgn.ResourceName
                      rgt 1
                      xlCol = (Asgn.Work / 480) & " Days"
                      rgt 1
                      xlCol = (Asgn.ActualWork / 480) & " Days"
                  Next Asgn
                  Tcount = Tcount + 1
              End If
          Next t
          AppActivate "Microsoft Project"
          
          MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
          End Sub
          Sub dwn(i As Integer)
          Set xlRow = xlRow.Offset(i, 0)
          End Sub
          
          Sub rgt(i As Integer)
          Set xlCol = xlCol.Offset(0, i)
          End Sub

          Comment


          • #6
            Re: Import Data from MS Project 2003

            I you don't want to see Project, don't make it visible.

            Also I don't quite see why you need AppActivate for either Excel or Project.

            You have created references to both applications which you shluld be able to use in the code.

            However, I don't actually know if you'll be able to avoid that message.
            Boo!

            Comment


            • #7
              Re: Import Data from MS Project 2003

              thanks for the help! My macro seems to be pulling in the data except it doesn't match the data that I see in Project? When the Asgn.StartVariance or Asgn.DurationVariance are 0 it works fine but all other numbers are wrong. Any ideas or other tips on my code are appreciated.

              Code:
              Option Explicit
              Dim xlRow As Excel.Range
              Dim xlCol As Excel.Range
              
              Sub TaskHierarchy()
              Dim xlApp As Excel.Application
              Dim xlBook As Excel.Workbook
              Dim xlSheet As Excel.Worksheet
              Dim t As Task
              Dim Asgn As Assignment
              Dim ColumnCount As Integer
              Dim Columns As Integer
              Dim Tcount As Integer
              Dim Proj As MSProject.Application
              
              Set Proj = CreateObject("MSProject.Application")
              Proj.Visible = False
              Proj.FileOpen Name:="\\frxfileserv2\Engrng\ProductEngineering\PV&V\Schedules\DT\DT Master Schedule.mpp", ReadOnly:=True
              
              
              Set xlApp = ThisWorkbook.Application
              'Set xlApp = New Excel.Application
              'xlApp.Visible = True
              'AppActivate "Microsoft Excel"
              
              Set xlBook = xlApp.ThisWorkbook
              Set xlSheet = xlBook.Worksheets.Add
              
              
              'xlSheet.Name = Proj.Name
              'AppActivate "Microsoft Project"
              'count columns needed
              ColumnCount = 0
              For Each t In ActiveProject.Tasks
                  If Not t Is Nothing Then
                      If t.OutlineLevel > ColumnCount Then
                          ColumnCount = t.OutlineLevel
                      End If
                  End If
              Next t
              
              'Set Range to write to first cell
              Set xlRow = xlApp.Range("A1")
              xlRow = "Filename: " & Proj.Name
              dwn 1
              xlRow = "OutlineLevel"
              dwn 1
              
              'label Columns
              For Columns = 1 To (ColumnCount + 1)
                  Set xlCol = xlRow.Offset(0, Columns - 1)
                  xlCol = Columns - 1
              Next Columns
              rgt 2
              xlCol = "Resource Name"
              rgt 1
              xlCol = "Start Variance [Days]"
              rgt 1
              xlCol = "Finish Variance [Days]"
              Tcount = 0
              For Each t In ActiveProject.Tasks
                  If Not t Is Nothing Then
                      dwn 1
                      Set xlCol = xlRow.Offset(0, t.OutlineLevel)
                      xlCol = t.Name
                          If t.Summary Then
                              xlCol.Font.Bold = True
                          End If
                      For Each Asgn In t.Assignments
                          dwn 1
                          Set xlCol = xlRow.Offset(0, Columns)
                          xlCol = Asgn.ResourceName
                          rgt 1
                          xlCol = (Asgn.StartVariance / 480)
                          rgt 1
                          xlCol = (Asgn.FinishVariance / 480)
                      Next Asgn
                      Tcount = Tcount + 1
                  End If
              Next t
              AppActivate "Microsoft Excel"
              
              MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
              End Sub
              Sub dwn(i As Integer)
              Set xlRow = xlRow.Offset(i, 0)
              End Sub
              
              Sub rgt(i As Integer)
              Set xlCol = xlCol.Offset(0, i)
              End Sub

              Comment


              • #8
                Re: Import Data from MS Project 2003

                How are the numbers wrong?
                Boo!

                Comment


                • #9
                  Re: Import Data from MS Project 2003

                  Data viewed in Project:
                  0 0
                  551 626
                  551 564
                  564 626
                  626 626
                  0 0

                  Data imported into excel:
                  0 0
                  85 143
                  85 98
                  98 143
                  143 143
                  0 0

                  I'm dividing the raw data by 480 because I read that the standard day in Project is 8 hours and the base unit is minutes therefore:

                  8*60=480

                  Maybe they are using a different base unit or length of day in this Project file. I know that they're all off by the same amount and I guess I can just divide by a different number to get the values to match but I'd like to figure out the root cause of the skewing.

                  Comment


                  • #10
                    Re: Import Data from MS Project 2003

                    I was wondering why you had that division.

                    I can't recall having to do any such conversion when I imported/exported from Project to Excel.
                    Boo!

                    Comment


                    • #11
                      Re: Import Data from MS Project 2003

                      Well without doing a conversion I get the following numbers:

                      Raw data not dividing by 480:
                      0 0
                      40800 68640
                      40800 47040
                      47040 68640
                      68640 68640

                      Data in Project:

                      0 days 0 days
                      551 days 626 days
                      551 days 564 days
                      564 days 626 days
                      626 days 626 days

                      And looking at it closer they're not all off by the same factor which is really strange.

                      Comment

                      Working...
                      X