Export from Excel to MS Project and Indenting

[VanderBoon]

Hello,

I have an Excel file while will be populated with various tasks associated with specific projects structured as below:

I am trying to write a macro to export data within to sheet to MS Project, with the programs forming a container for sub tasks as below:

Here's an extract I have of the code so far, I'm struggling with a way of using the program names as level one tasks whilst also looping through to pull the underlying tasks in.

Sub Test()
Dim PrjApp As Object
Dim NewPrj As Object

Set PrjApp = CreateObject("MSProject.Application")
Set NewPrj = PrjApp.Projects.Add

Dim wst As Worksheet

Set wst = ThisWorkbook.Worksheets("Planned Tests")

Dim i As Byte
Dim j As Long
Dim s As Byte
Dim PrjNamePrev As String
Dim PrjNameCur As String

PrjApp.ProjectSummaryInfo Calendar:="24 Hours"

i = 2

NewPrj.Tasks.Add
NewPrj.Tasks(i - 1).OutlineLevel = 1
NewPrj.Tasks(i - 1).Name = wst.Cells(i, 2)
NewPrj.Tasks(i - 1).ResourceNames = wst.Cells(i, 3)

NewPrj.Tasks.Add
NewPrj.Tasks(i).OutlineLevel = 2
NewPrj.Tasks(i).Name = wst.Cells(i, 5)
NewPrj.Tasks(i).Start = CDate(wst.Cells(i, 7))
NewPrj.Tasks(i).Finish = CDate(wst.Cells(i, 8))
NewPrj.Tasks(i).ResourceNames = wst.Cells(i, 3)

j = Sheets("Planned Tests").Range("F" & Rows.Count).End(xlUp).Row

For i = 3 To j

    PrjNamePrev = wst.Cells(i - 1, 2)
    PrjNameCur = wst.Cells(i, 2)

If PrjNameCur = PrjNamePrev Then



    NewPrj.Tasks.Add
    NewPrj.Tasks(i).OutlineLevel = 2
    NewPrj.Tasks(i).Name = wst.Cells(i, 5)
    NewPrj.Tasks(i).Start = CDate(wst.Cells(i, 7))
    NewPrj.Tasks(i).Finish = CDate(wst.Cells(i, 8))
    NewPrj.Tasks(i).ResourceNames = wst.Cells(i, 3)

Else

    NewPrj.Tasks.Add
    NewPrj.Tasks(i).OutlineLevel = 1
    NewPrj.Tasks(i).Name = wst.Cells(i, 2)
    NewPrj.Tasks(i).ResourceNames = wst.Cells(i, 3)
        
    NewPrj.Tasks.Add
    NewPrj.Tasks(i + 1).OutlineLevel = 2
    NewPrj.Tasks(i + 1).Name = wst.Cells(i, 5)
    NewPrj.Tasks(i + 1).Start = CDate(wst.Cells(i, 7))
    NewPrj.Tasks(i + 1).Finish = CDate(wst.Cells(i, 8))
    NewPrj.Tasks(i + 1).ResourceNames = wst.Cells(i, 3)
    
   


Stop

End If

Next i
End Sub

I know the issue, as it overwrites line 7 in MS Project with Task 6, etc, but cannot figure out how to overcome this, or a better way of writing the macro.

I'm a relative Noob when it comes to VBA, as you can probably tell! But any help would be greatly appreciated.

Thank you!

 

[Trevor G]

You can record macros in MS Project and perhaps looking to import the spreadsheet via a recorded macro in Project could assist with the code that could be adjusted to export from Excel VBA.