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

[vba]

newproj.resources.Add.Name = Strresource
[/vba]

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

Have you looked on the Resources sheet?

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?

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.

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?

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]

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.

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]

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.

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??????????

Do you mean just simply insert columns into an Excel spreadsheet, and delete same?

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.

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!

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