[Resolved]MS Project VBA, the red-headed stepchild

[queenb2005]

I'm writing code that takes cell strings in Excel and attempts to match them with resource codes in MS Project. It's a pain and there is very little information I could find that helps. If anyone could give me a link of a forum that deals with MS Project I would really appreciate it. Here's a sample of my chicken scratch play code just to get anything to populate.

Dim ObjTask As String, SourceTask As Task
Dim appXL As Excel.Application
Dim R As Long, Names As String
Dim hours As Double
Dim N As Long
Rws = 3
Sheets("Summary Hrs").Select
While Rws < 650
TaskName = Cells(Rws, "B").Text
If TaskName <> "" Then
WindowActivate WindowName:="C:\Documents and Settings\me\Desktop\project_template.Published.mpp"
 
SelectTaskField Row:=1, Column:="Name", RowRelative:=False
For R = 1 To ActiveProject.Tasks.Count
 
    ObjTask = ActiveProject.Tasks(R).Name
    If ObjTask = TaskName Then
        Sheets("Summary Hrs").Select
        Rws2 = Rws + 1
        While Cells(Rws2, "C").Text <> ""
            rescode = Cells(Rws2, "C").Text
            hours = Cells(Rws2, "E").Text
            Names = rescode & ", " & Names
            Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))
            Rws2 = Rws2 + 1
        Wend
        SetTaskField Field:="Resource Names", Value:=Names, AllSelectedTasks:=True
        R = ActiveProject.Tasks.Count
        'WindowActivate WindowName:="C:\Documents and Settings\me\Desktop\project_template.Published.mpp"
            'SelectTaskField Row:=R, Column:="Name", RowRelative:=False
            'SetTaskField Field:="Resource Names", Value:=AllResources, AllSelectedTasks:=True
            'ResourceAssignment Resources:=rescode, Operation:=pjAssign
            'WindowActivate TopPane:=False
            'SetTaskField Field:="Work", Value:=hours, TaskID:=R, ProjectName:="C:\Documents and Settings\me\Desktop\project_template.Published.mpp"
    End If
Next R
End If
Rws = Rws + 1
Wend
End Sub

I tried it a few ways. Just dumping all the gathered resource codes puts them in the first line my my project only. I'm sure it's just a simple error, it was working better earlier. After 8 hours of this, it's hard to keep starit what you've changed in your code and what made it break last.
Any advice will help.
Thanks,
B

[Bartender]

Sorry if this isn't an intelligent response, but I'm not very experienced in programming.

You write:

For R = 1 To ActiveProject.Tasks.Count
 
    ObjTask = ActiveProject.Tasks(R).Name
    If ObjTask = TaskName Then
        Sheets("Summary Hrs").Select
        Rws2 = Rws + 1
        While Cells(Rws2, "C").Text <> ""
            rescode = Cells(Rws2, "C").Text
            hours = Cells(Rws2, "E").Text
            Names = rescode & ", " & Names
            Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))
            Rws2 = Rws2 + 1
        Wend
        SetTaskField Field:="Resource Names", Value:=Names, AllSelectedTasks:=True
        R = ActiveProject.Tasks.Count 'Note this line
       End If
Next R

When you set R to the ending condition of your for loop right in the middle of the loop itself, that should cause your loop to quit after the first iteration.

Admittedly, I don't really see what your code is doing, I just went through to see if I could find any simple problems.
Good Luck!

[queenb2005]

I ended up re-wring that code a different way. It works now. The issue now is understanding MS Project. I am trying to select a cell, which I can, and then return a string for what text is in it, which I just can't figure out. Here is an exact picture of the project and what I can't identify.

I am trying to select a resource code, store it as a string, match that string to a value in Excel spreadsheet, then fill out the hours accordingly. I just can't return a value, it seems so simple but I just can't make any code work.

Rws = 1
ViewApply Name:="Tas&k Usage"
SelectTaskField Row:=1, Column:="Name", RowRelative:=True
For R = 2 To ActiveProject.Tasks.Count
    ObjTask = ActiveProject.Tasks(R).Name
    Sheets("Summary Hrs").Activate
    Columns("B:B").Select
    Selection.Find(What:=ObjTask, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    Rws = ActiveCell.Row + 1
    If Cells(Rws, "C") <> "" Then
        SelectTaskField Row:=0, Column:="Name"
        'below selects the resource code's cell
        SelectTaskField Row:=1, Column:="Name"
        'MyResource is nothing. I've tried different
        'ways of using ActiveProject or ActiveSelection
        'with no avail
        MyResource = ActiveSelection.FieldName
        
    'this is where I go to excel, match the value
    'grab the "hours" value, then stick it where it goes
        SetResourceField Field:="Work", Value:=hours, AllSelectedResources:=True
        
    End If
Next R

So any suggestions????

QueenB

[queenb2005]

With the help of Project 2003 Inside Out, Using Excel Visual Basic Second Edition, both program's help files, and four pots of coffee it finally clicked. Here is how to specify a Project file and auto populate your resources and thier hours from an Excel spreadsheet before you start the project. I used Text10 as a static field in case more milestones are added.

Sub Resource_Load()
 
On Error Resume Next
Application.ScreenUpdating = False
Dim TaskNumber As Integer
Dim R As Integer
Dim N As Integer
Rws = 1
Rws2 = 1
N = 0
 
PJFile = Application.GetOpenFilename("All Files (*.*),*.*", 1, "Select MS Project Template", MultiSelect = False)
Dim appPJ As MSProject.Application
Set appPJ = CreateObject("MSProject.Application")
appPJ.Visible = True
appPJ.FileOpen Name:=PJFile, ReadOnly:=False, FormatID:="MSProject.MPP"
WindowSplit
 
ViewApply Name:="Tas&k Usage"
SelectTaskField Row:=1, Column:="Name", rowrelative:=False
 
For R = 1 To ActiveProject.Tasks.Count
    TaskNumber = ActiveProject.Tasks(R).Text10
    R = TaskNumber
    SelectTaskField Row:=R + N, Column:="Name", rowrelative:=False
    ObjTask = ActiveProject.Tasks(R).Name
    Sheets("Summary Hrs").Activate
    Columns("A:A").Select
    Selection.Find(What:=TaskNumber, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    Rws = ActiveCell.Row
    Rws2 = Rws + 1
    If Cells(Rws2, "C") <> "" Then
        While Cells(Rws2, "E").Text <> ""
            rescode = Cells(Rws2, "C").Text
            hours = Cells(Rws2, "E").Text
            WindowActivate WindowName:=PJFile
            SelectTaskField Row:=0, Column:="Name", rowrelative:=True
            ResourceAssignment Resources:=rescode & "[0%]", Operation:=pjAssign
            Rws2 = Rws2 + 1
        Wend
        SelectTaskField Row:=1, Column:="Name"
        For Each A In ActiveProject.Tasks(R).Assignments
            ResourceName = A.ResourceName
            Sheets("Summary Hrs").Activate
            Columns("C:C").Select
            Selection.Find(What:=ResourceName, After:=Cells(Rws, "C"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
            Rws2 = ActiveCell.Row
            hours = Cells(Rws2, "E").Value
            WindowActivate WindowName:=PJFile
            SetResourceField Field:="Work", Value:=hours, AllSelectedResources:=True
            SelectTaskField Row:=1, Column:="Name"
            N = N + 1
            Rws2 = 1
        Next A
    End If
Next R
 
End Sub

Project is not too hard, just different.
~ Queen B

[thor77]

I've looked all over and haven't found a way to start a new thread. This one is close to what my subject is so I'll reply.

If I run the below code on one MS Project file it works. On another one and I get a Run-time error '1101': The argument value is not valid. I've narrowed it down to the RowRelative:=False argument. Does anyone know why it would work on one file and not on the other? Thank you!

Sub CountAllTasks()
Dim T As Task
Dim i As Integer
Dim tCount As Integer
Dim r As Long
Dim sTest1 As String


i = 0
tCount = 0

tCount = ActiveProject.tasks.Count


For r = 0 To tCount

SelectTaskField Row:=r, Column:="Name", RowRelative:=False


sTest1 = ActiveCell.Task.Name
On Error GoTo ErrHandler:


If sTest1 <> "" Then
i = i + 1
End If
Next r

ErrHandler:
MsgBox "Number of active tasks in this file: " & i, vbOKCancel
SelectRow Row:=0, RowRelative:=False



End Sub