Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,541

    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,541

    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.

    VB:
    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,541

    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.

    VB:
    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,541

    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,541

    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