Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 40

Thread: Solved: Exporting from Excel to MS Project

  1. #1
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location

    Solved: Exporting from Excel to MS Project

    Hi.

    Im a beginner to VBA in general and using it with Excel and this may seem a dumb question but here goes.

    Is there a way that i can export data from a specific sheet within excel to MS Project? My excel workbook contains team information from which everybody is assigned tasks and they put a time on those tasks. What i want is to transfer the name of team member and the amount of time (calculated by start and end dates) worked into MS Project.

    Can this be done? Any help or information would be greatly appreciated.

    Thanks.
    DcD
    Thanks,

    Dean

  2. #2
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Dean,

    Per se it does not exist any dumb questions only relevant but it may exist stupid answers

    From which software do You want to do it? From Excel or from Project?

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  3. #3
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Dennis,
    Thanks for your reply. I was looking to do it through excel. Currently i have a project set up where everybody enters their time details, if i can somehow export this into MS Project that would be great.
    Any suggestions greatly appreciated.

    Thanks,
    Dean.
    Thanks,

    Dean

  4. #4
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi Dean,

    Below You find an example based on the information You so far has been given:

    [vba]
    Option Explicit
    Sub Add_Data_Project()
    'Add a reference to Microsoft Project x.x Object Library
    'in the VB-editor through the command Tools | References..
    'The x.x stands for version like 11.0 or previously.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim stTeammember As String, stTask As String
    Dim dtStartDate As Date
    Dim lnTime As Long
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    'Populate the variables with values from the worksheet.
    With wsSheet
    stTeammember = .Range("D5").Value
    stTask = .Range("E5").Value
    dtStartDate = .Range("F5").Value
    lnTime = .Range("G5").Value
    End With
    'Instantiate and open MS Project and the project.
    Dim prApp As MSProject.Application
    Dim prProject As MSProject.Project
    Set prApp = New MSProject.Application
    prApp.FileOpen "c:\VBAX.mpp"
    Set prProject = prApp.ActiveProject
    'Add task and other wanted information to the project.
    With prProject
    .Tasks.Add stTask
    With .Tasks(stTask)
    .Duration = lnTime & " days"
    .ResourceNames = stTeammember
    .Start = dtStartDate
    End With
    End With
    'Save the project and close MS Project.
    With prApp
    .FileSave
    .Quit
    End With
    MsgBox "The project VBAX has successfully been updated!", vbInformation
    'Release objects from memory.
    Set prProject = Nothing
    Set prApp = Nothing
    End Sub
    [/vba]

    It works well with my copy of MS Project 2003 Pro. I'll check in tomorrow (my local time) to take part of Your findings.

    BTW, You may consider to check that all wanted data is available in the sheet before export it to the project.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #5
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Dennis,

    Thanks again for your reply. Sorry for delay in response.

    When i run what you provided I get errors in the section
    .Start = dtStartDate
    It says "Application defined or object defined error"??

    Im not really certain what the message is telling me. Either the .start isnt a part of the Project Application or this should be an object, but im not sure how to resolve.

    Any suggestions would be greatly appreciated.

    Thanks again for all your help
    Dean

  6. #6
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Dennis,

    Pls disregard last msg, as i tracked it down. It was my stupid mistake. Had chosen the incorrect WorkSheet.

    Thanks for your help. Once i corrected my error all is resolved.

    THANKYOU.
    Thanks,

    Dean

  7. #7
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Dennis,

    One question. With what you have coded, how would you cater for more than one Team Member or Task etc? Help is greatly appreciated.

    Thanks.
    Dean.

  8. #8
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi Dean,


    Glad You managed to sort it out and below You find one example how to add several tasks, members etc to a project.

    [vba]
    Option Explicit
    Sub Add_Several_Data_Project()
    'Add a reference to Microsoft Project x.x Object Library
    'in the VB-editor through the command Tools | References..
    'The x.x stands for version like 11.0 or previously.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaTeammembers As Variant, vaTasks As Variant
    Dim vaStartDates As Variant, vaTime As Variant
    Dim lnStart As Long, lnCounter As Long
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    'Populate the variables with values from the worksheet.
    With wsSheet
    lnStart = .Range("D65536").End(xlUp).Row
    vaTeammembers = .Range("D5" & lnStart).Value
    vaTasks = .Range("E5:E" & lnStart).Value
    vaStartDates = .Range("F5:F" & lnStart).Value
    vaTime = .Range("G5:G" & lnStart).Value
    End With
    'Instantiate and open MS Project and the project.
    Dim prApp As MSProject.Application
    Dim prProject As MSProject.Project
    Set prApp = New MSProject.Application
    prApp.FileOpen "c:\VBAX.mpp"
    Set prProject = prApp.ActiveProject
    'Add task and other wanted information to the project.
    With prProject
    For lnCounter = 1 To UBound(vaTasks)
    .Tasks.Add vaTasks(lnCounter, 1)
    With .Tasks(vaTasks(lnCounter, 1))
    .Duration = vaTime(lnCounter, 1) & " days"
    .ResourceNames = vaTeammembers(lnCounter, 1)
    .Start = vaStartDates(lnCounter, 1)
    End With
    Next lnCounter
    End With
    'Save the project and close MS Project.
    With prApp
    .FileSave
    .Quit
    End With
    MsgBox "The project VBAX has successfully been updated!", vbInformation
    'Release objects from memory.
    Set prProject = Nothing
    Set prApp = Nothing
    End Sub
    [/vba]

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  9. #9
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Worked perfectly.

    Thanks very much...
    Thanks,

    Dean

  10. #10
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    You're welcome and I may some day make a write up about automation of MS Project from MS Excel.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  11. #11
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    That would be handy for me!! Definitely.

    Once again thanks Dennis.

  12. #12
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Dennis,

    Sorry to bother you, but im having some troubles. It is doubling up the tasks in Project.

    i know its because of this:

    With prProject
    For lnCounter = 1 To UBound(vaTasks)
    .Tasks.Add vaTasks(lnCounter, 1) --> This section
    With .Tasks(vaTasks(lnCounter, 1))
    .Duration = vaTime(lnCounter, 1) & " days"
    .ResourceNames = vaTeammembers(lnCounter, 1)
    .Start = vaStartDates(lnCounter, 1)
    End With
    Next lnCounter
    End With


    I tried to do a condition that if the task existed in Project, then skip this add option, however i cant get it to find a match. Any assistance greatly appreciated...

    Thanks.

  13. #13
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi,


    See if the following revised solutions is workable for You:

    [vba]
    Sub Add_Several_Data_Project()
    'Add a reference to Microsoft Project x.x Object Library
    'in the VB-editor through the command Tools | References..
    'The x.x stands for version like 11.0 or previously.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaTeammembers As Variant, vaTasks As Variant
    Dim vaStartDates As Variant, vaTime As Variant
    Dim lnStart As Long, lnCounter As Long
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    'Populate the variables with values from the worksheet.
    With wsSheet
    lnStart = .Range("D65536").End(xlUp).Row
    vaTeammembers = .Range("D5" & lnStart).Value
    vaTasks = .Range("E5:E" & lnStart).Value
    vaStartDates = .Range("F5:F" & lnStart).Value
    vaTime = .Range("G5:G" & lnStart).Value
    End With
    'Instantiate and open MS Project and the project.
    Dim prApp As MSProject.Application
    Dim prProject As MSProject.Project
    Dim prTask As MSProject.Task
    Dim prResource As MSProject.Resource
    Dim bFlagTask As Boolean
    Dim bFlagResource As Boolean
    Set prApp = New MSProject.Application
    prApp.FileOpen "c:\VBAX.mpp"
    Set prProject = prApp.ActiveProject
    'Add task and other wanted information to the project.
    With prProject
    For lnCounter = 1 To UBound(vaTasks)
    bFlagResource = False
    'Loop through the collection of present resources and check if a resource
    'exist or not.
    For Each prResource In prProject.Resources
    If prResource.Name = vaTeammembers(lnCounter, 1) Then bFlagResource = True
    Next prResource
    'Add the resource to the project and add a rate to it.
    If bFlagResource = False Then
    .Resources.Add vaTeammembers(lnCounter, 1)
    With .Resources(vaTeammembers(lnCounter, 1))
    .StandardRate = 100
    .OvertimeRate = 100 * 1.5
    End With
    End If
    bFlagTask = False
    'Loop through the collection of present tasks and check if a task
    'exist or not.
    For Each prTask In prProject.Tasks
    If prTask.Name = vaTasks(lnCounter, 1) Then bFlagTask = True
    Next prTask
    If bFlagTask = False Then
    .Tasks.Add vaTasks(lnCounter, 1)
    With .Tasks(vaTasks(lnCounter, 1))
    .Duration = vaTime(lnCounter, 1) & " days"
    .ResourceNames = vaTeammembers(lnCounter, 1)
    .Start = vaStartDates(lnCounter, 1)
    End With
    End If
    Next lnCounter
    End With
    'Save the project and close MS Project.
    With prApp
    .FileSave
    .Quit
    End With
    MsgBox "The project VBAX has successfully been updated!", vbInformation
    'Release objects from memory.
    Set prResource = Nothing: Set prTask = Nothing
    Set prProject = Nothing: Set prApp = Nothing
    End Sub
    [/vba]

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  14. #14
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Thanks heaps.

    It works perfectly. Thankyou!
    Thanks,

    Dean

  15. #15
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Dean,

    Thanks for the feedback and You're welcome. I've made a note that I will make a write up about automating MS Project from Excel and transfer data.

    Kind regards.
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  16. #16
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Hi Dennis.

    Ive run into a problem. When others use excel workbook and they dont have ms project it gives them debug errors. Is there a way where i can disable the MS Project reference for users that do not have ms project?

    Ive tried but with no luck what so ever.

    Thanks
    Thanks,

    Dean

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Not tested, but the principle seems sound

    [VBA]Option Explicit

    Sub Add_Several_Data_Project()
    'Add a reference to Microsoft Project x.x Object Library
    'in the VB-editor through the command Tools | References..
    'The x.x stands for version like 11.0 or previously.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaTeammembers As Variant, vaTasks As Variant
    Dim vaStartDates As Variant, vaTime As Variant
    Dim lnStart As Long, lnCounter As Long
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    'Populate the variables with values from the worksheet.
    With wsSheet
    lnStart = .Range("D65536").End(xlUp).Row
    vaTeammembers = .Range("D5" & lnStart).Value
    vaTasks = .Range("E5:E" & lnStart).Value
    vaStartDates = .Range("F5:F" & lnStart).Value
    vaTime = .Range("G5:G" & lnStart).Value
    End With
    'Instantiate and open MS Project and the project.
    Dim prApp As Object 'MSProject.Application
    Dim prProject As Object 'MSProject.Project
    Dim prTask As Object 'MSProject.Task
    Dim prResource As Object 'MSProject.Resource
    Dim bFlagTask As Boolean
    Dim bFlagResource As Boolean
    On Error Resume Next
    Set prApp = CreateObject("MSProject.Application")
    On Error GoTo 0
    If Not prApp Is Nothing Then
    prApp.FileOpen "c:\VBAX.mpp"
    Set prProject = prApp.ActiveProject
    'Add task and other wanted information to the project.
    With prProject
    For lnCounter = 1 To UBound(vaTasks)
    bFlagResource = False
    'Loop through the collection of present resources and check if a resource
    'exist or not.
    For Each prResource In prProject.Resources
    If prResource.Name = vaTeammembers(lnCounter, 1) Then bFlagResource = True
    Next prResource
    'Add the resource to the project and add a rate to it.
    If bFlagResource = False Then
    .Resources.Add vaTeammembers(lnCounter, 1)
    With .Resources(vaTeammembers(lnCounter, 1))
    .StandardRate = 100
    .OvertimeRate = 100 * 1.5
    End With
    End If
    bFlagTask = False
    'Loop through the collection of present tasks and check if a task
    'exist or not.
    For Each prTask In prProject.Tasks
    If prTask.Name = vaTasks(lnCounter, 1) Then bFlagTask = True
    Next prTask
    If bFlagTask = False Then
    .Tasks.Add vaTasks(lnCounter, 1)
    With .Tasks(vaTasks(lnCounter, 1))
    .Duration = vaTime(lnCounter, 1) & " days"
    .ResourceNames = vaTeammembers(lnCounter, 1)
    .Start = vaStartDates(lnCounter, 1)
    End With
    End If
    Next lnCounter
    End With
    'Save the project and close MS Project.
    With prApp
    .FileSave
    .Quit
    End With
    MsgBox "The project VBAX has successfully been updated!", vbInformation
    'Release objects from memory.
    Set prResource = Nothing: Set prTask = Nothing
    Set prProject = Nothing: Set prApp = Nothing
    End If
    End Sub
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    I agree with Bob and just to clarify:

    If You at present have set a reference to the external library of MS Project in You workbook then remove it as the revised sample is based on late binding which make it possible to evaluate if MS Project exist or not on the running machine.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  19. #19
    VBAX Regular
    Joined
    Aug 2005
    Posts
    16
    Location
    Sorry. I should clarify.

    Basically i created this 'tool' to track team members, with the assistance of yourself. Each team member opens excel and fills in what they have done. All members have there own machines, except some dont have MS Project, with no need of it.

    I have ms project, and the reference is in excel. When they open it, they dont have ms project and in there vba references there is a "MISSING : Ms Project 11.0 Object Library". The problem happens with other code in different sheets when the MISSING reference is ticked it doesnt recognise Chr. Untick the missing reference library fixes the problem. They dont need the "Add to Project" Command as it is only for team leaders.

    Could you create something like a button or checkbox that when selected will turn on/off that reference? Is this possible?

    Thanks heaps.
    Thanks,

    Dean

  20. #20
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    [vba]lnStart = .Range("D65536").End(xlUp).Row[/vba]

    .. hmm, xld, are you digressing from your own standings on obtaining the last row??

Posting Permissions

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