Consulting

Results 1 to 16 of 16

Thread: Create MS project file from excel

  1. #1
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    Create MS project file from excel

    Hi,

    Found the following code that enable to generate MS project file from excel,but I'm the Resource value.
    Any help will be appriciated.

    10X

    See the following code:

    Sub test()
    Dim pjapp As Object
    Dim strValue, strStartDate, strEndDate, Strresource As String
    Dim newproj
    Set pjapp = CreateObject("MSProject.application")
    'this checks to see if a valid object has been created. If not it pops up
    'a warning and then quits. Users without Project installed will see this message.
    If pjapp Is Nothing Then
    MsgBox "Project is not installed"
    End
    End If
    'now that we have an application we make it visible
    pjapp.Visible = True
    'we add a new project
    Set newproj = pjapp.Projects.Add
    'Alternative: open existing MSProject file
    'pjapp.Application.FileOpen "test_export.mpp"
    'we set the title property (you can do whatever you want here.
    newproj.Title = "My New Project"
    'we make the new project the active project
    Set ActiveProject = newproj
    'and finally we add a new task to the project
    'strValue = "yossi"
    ' strStartDate = "19/07/09"
    ' strEndDate = "28/07/09"




    For I = 2 To 4

    strValue = Worksheets("Sheet1").Range("A" & I)
    strStartDate = Worksheets("Sheet1").Range("B" & I)
    strEndDate = Worksheets("Sheet1").Range("C" & I)
    Strresource = Worksheets("Sheet1").Range("D" & I)
    newproj.Tasks.Add (strValue & " " & Strresource)
    newproj.Tasks(I - 1).Start = strStartDate
    newproj.Tasks(I - 1).Finish = strEndDate
    ' newproj.resource(I - 1).Name = Strresource (it's not working..)
    Next I
    ' pjapp.FileSave
    End Sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    newproj.resources.Add.Name = Strresource
    [/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

  3. #3
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    Red face Great- it almost work..

    It almost works,when I'm running the macro I don't get any error message as I I used to before,but,the resource value dosen't appear in the MS project file...
    Any idea??

    10X

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Have you looked on the Resources sheet?
    ____________________________________________
    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    ANother thought, are those resources already in the project and do you want to just allocate them to that task, just add them to the project, or both?
    ____________________________________________
    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

  6. #6
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    It wroks!! but..

    It was "hidden" in the drop down list of resources.
    How can I determine it that it will be the defualt,without opening the drop down list and choosing it?

    10X.

  7. #7
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    As a matter of fact I have noticed.

    that the command :
    newproj.resources.Add.Name = Strresource

    adds all the resources to the drop down list.
    I nned that for each tasks there will be specific resource name.
    Any ideas?

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    iS THIS WHAT YOU MEAN?

    [VBA]

    Sub test()
    Dim pjapp As Object
    Dim strValue, strStartDate, strEndDate, Strresource As String
    Dim newproj
    Set pjapp = CreateObject("MSProject.Application")
    If pjapp Is Nothing Then
    MsgBox "Project is not installed"
    Exit Sub
    End If
    pjapp.Visible = True

    Set newproj = pjapp.Projects.Add
    newproj.Title = "My New Project"
    Set ActiveProject = newproj
    For i = 2 To 4

    strValue = Worksheets("Sheet1").Range("A" & i)
    strStartDate = Worksheets("Sheet1").Range("B" & i)
    strEndDate = Worksheets("Sheet1").Range("C" & i)
    Strresource = Worksheets("Sheet1").Range("D" & i)
    newproj.Tasks.Add (strValue & " " & Strresource)
    newproj.Tasks(i - 1).Start = strStartDate
    newproj.Tasks(i - 1).Finish = strEndDate
    If Not ExistsInCollection(newproj.Resources, Strresource) Then _
    newproj.Resources.Add.Name = Strresource
    newproj.Tasks(i - 1).ResourceNames = Strresource
    Next i
    End Sub

    Public Function ExistsInCollection(pColl, ByVal pKey As String) As Boolean
    On Error GoTo NoSuchKey
    If VarType(pColl.Item(pKey)) = vbObject Then
    ' force an error condition if key does not exist
    End If
    ExistsInCollection = True
    Exit Function

    NoSuchKey:
    ExistsInCollection = False
    End Function
    [/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

  9. #9
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    Smile Thank you very much..

    It works 100% !!
    I got what I need.
    last question:

    how can I control the MS project output:

    1.Currently I get the output in "weeks" and I would like to get it in "days".
    2. I get fields that I would like to hide them such as "Duration" and "predecessors".
    3. the widith of the colums - the days and the rest such as "Resource" "Task" etc.

    Thanks for your help,it realy helps me to finish this task.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    MS Project has many views, which are you specifically thinking of? I am showing the Gantt view, and that is showing in days.

    To delete columns, use

    [vba]

    SelectTaskColumn Column:="Duration"
    ColumnDelete
    SelectTaskColumn Column:="Predecessors"
    ColumnDelete
    [/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

  11. #11
    VBAX Regular
    Joined
    Jul 2009
    Posts
    6
    Location

    it works only from MS project...

    It means that if I paste this code in MS project it works.,
    But how can I use this code from the excel as part of the macro that I'm using to create the MS project file?

    10X alot.

  12. #12
    Good stuff here...

    I'm looking to show columns and delete columns from excel vba - can you offer any assistance there? There's not much out there in the terms of resources to help people between Excel and MS Project thanks god for forums.

    One last thing, Outline levels. I set up my excel sheet to carry over outline levels but run into a road block with the code to do so, any help?

    .outline level = range??????????

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Do you mean just simply insert columns into an Excel spreadsheet, and delete same?
    ____________________________________________
    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

  14. #14
    insert columns (fields) in MS Project from vba in excel. WHat i have is a workbook that generates all the data for a MS Project schedule. I've created a hidden page that mimics the schedule and I want to use it to build the schedule in PRoject. I can transfer the task and hours, need to keep the Outline level in particular order and make some fields (columns) disappera and others appear.

    Thanks for your help.

  15. #15
    VBAX Newbie
    Joined
    Feb 2011
    Posts
    1
    Location
    I need to do something similar to this - though when I run the script above it stops at the strStartDate with the argument not valid.
    I tried declaring strStartDate/Finish as Dates but no luck.
    How do I parse the Start/Finish dates to the Ms Project Plan from Ms Excel?

    Thanks!

  16. #16
    VBAX Regular
    Joined
    Feb 2011
    Posts
    13
    Location
    Quote Originally Posted by massimop
    I need to do something similar to this - though when I run the script above it stops at the strStartDate with the argument not valid.
    I tried declaring strStartDate/Finish as Dates but no luck.
    How do I parse the Start/Finish dates to the Ms Project Plan from Ms Excel?

    Thanks!
    How are the dates formatted in excel? I've run into no end of problems trying to get excel and project working together with dates - especially since I work for a multinational, so half the reports I run are in dd/mm/yyyy format, and half are in mm/dd/yyyy format.

    I have good results using dates in excel formatted as strings in "dd/mmm/yyy" format - MS project seems to recognise that.

    Chris

Posting Permissions

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