VBA - Import Excel data to MS Project

Question

I'm trying to take a table of data from Excel and import it into MS Project

Here is a screenshot of what I have in Excel:

CC: Excel table of column headers of: WBS, Task Name, Start Date, Finish Date, Duration, Work, and Resource Name with rows of data that are independent of resource name assignment.

Here is a screenshot of what I am looking for a VBA code to be able to produce from Excel to MS Project:

CC: MS Project file showing columns of WBS, Task Name, Start Date, Finish Date, Duration, Work, and Resource Name with resource names grouped by WBS.

I've tried copy and paste, but there has got to be a better option with VBA (I hope?)

If there are questions, I'm happy to answer them.

I really appreciate any help anyone can give me!

EDIT: Here is the VBA I have now:

Sub ExceltoProject()
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 = "ExcelExtract"
Set ActiveProject = newproj
For i = 2 To 4

strWBS = Worksheets("LABOR_IMS_INPUT").Range("A" & i)
strTaskName = Worksheets("LABOR_IMS_INPUT").Range("B" & i)
strStartDate = Worksheets("LABOR_IMS_INPUT").Range("C" & i)
strEndDate = Worksheets("LABOR_IMS_INPUT").Range("D" & i)
strDuration = Worksheets("LABOR_IMS_INPUT").Range("E" & i)
Strresource = Worksheets("LABOR_IMS_INPUT").Range("F" & i)
strWork = Worksheets("LABOR_IMS_INPUT").Range("G" & i)

newproj.Tasks.Add (strValue & " " & Strresource)
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

But this is what I get:

CC: Excel file and MS Project file. MS Project file only has "resource sheet name" data.

Any ideas what is happening. That I'm doing wrong?

Sorry for previous issues with clarity, I am visually impaired and trying to code!

If all you really need to do is get that data from Excel into Project, copy paste works well once you combine the rows in Excel so that you have just one row for each task (e.g. combine the resources). It can be done with VBA, automating Project from Excel or vice versa. — Rachel Hettinger, May 05 '21 at 01:03
rachel, thanks for your comment, i have something to take the data from project to excel, im just struggling to develop the VBA that would take the data from excel and put it in project. — vba-67678, May 06 '21 at 16:19
Show what you have and ask specific questions about where your code isn't working. This is what stackoverflow is about--getting help with specific questions. It's not really a please-write-the-whole-program-for-me kind of site. Check out [How do I ask a good question](https://stackoverflow.com/help/how-to-ask). — Rachel Hettinger, May 06 '21 at 18:09
hey rachel, thanks for your comment. really helps, honest. i'm new here so my apologies if im rusty. i've edited the above to hopefully try and explain this better. let me know if it is what you're looking for — vba-67678, May 06 '21 at 20:39

score 1 · Answer 1 · answered May 07 '21 at 00:14

This code will take the data from the Excel sheet to create a new Project schedule. No need to set both Finish and Duration fields as the Finish date will be determined by the Start date and Duration.

Sub ExceltoProject()
Dim pjapp As Object
Dim newproj As Object

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 = "ExcelExtract"

Dim wst As Worksheet
Set wst = ThisWorkbook.Worksheets("LABOR_IMS_INPUT")

Dim i As Long
For i = 2 To 4
    newproj.Tasks.Add
    newproj.Tasks(i - 1).Name = wst.Cells(i, 2)
    newproj.Tasks(i - 1).WBS = wst.Cells(i, 1)
    newproj.Tasks(i - 1).Start = CDate(wst.Cells(i, 3))
    newproj.Tasks(i - 1).Duration = wst.Cells(i, 5) & "d"
    newproj.Tasks(i - 1).ResourceNames = wst.Cells(i, 7)
    newproj.Tasks(i - 1).Work = wst.Cells(i, 6) & "h"
Next i

End Sub

sorry i hit enter too soon for the above. my apologies. hi rachel. thank you so much for this. i am getting a "run-time error '1101' and when i run the debug it highlights: "newproj.Tasks(i - 1).Start = CDate(wst.Cells(i, 3))" i'm trying to see where it is pulling and troubleshooting a solution. just to give you an update. — vba-67678, May 10 '21 at 16:08

VBA - Import Excel data to MS Project

1 Answers1