Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Import Data from MS Project 2003

  1. #1
    Join Date
    25th October 2005
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,542

    Re: Import Data from MS Project 2003

    Canuck

    I'm a little confused.

    Where are you importing/exporting to/from?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th October 2005
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,542

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th October 2005
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,542

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th October 2005
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,542

    Re: Import Data from MS Project 2003

    How are the numbers wrong?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th October 2005
    Posts
    17

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th July 2004
    Posts
    10,542

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 7
    Last Post: October 27th, 2010, 18:41
  2. Import Data From Access 2003
    By VinVin in forum Excel and/or SQL Help
    Replies: 6
    Last Post: May 3rd, 2008, 09:13
  3. Import Data: Import Text Files
    By Dave Hawley in forum Free Microsoft Excel 2003 Tutorials
    Replies: 0
    Last Post: October 23rd, 2007, 13:34
  4. Export And Import Module To Another Project (vba)
    By BorneoHornbill in forum EXCEL HELP
    Replies: 11
    Last Post: October 4th, 2006, 10:57
  5. Replies: 3
    Last Post: November 11th, 2005, 12:01

Bookmarks

Posting Permissions

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