Import Data from MS Project 2003

[Canuck]

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

[norie]

Canuck

I'm a little confused.

Where are you importing/exporting to/from?

[Canuck]

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

[norie]

Sorry I'm still confused.

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

How is it not working for you?

[Canuck]

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.

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 

[norie]

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.

[Canuck]

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.

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 

[norie]

How are the numbers wrong?

[Canuck]

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.

[norie]

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.