Importing Data from Excel and assigning WBS levels

[Palmcrest]

Hi, I have some data in Excel that I would like to import and assign hirarchy upon importing .
For example:
Column A has a list of work order numbers
Column B has a list of task numbers
Column C has task description
Column D has Work order header description.

I would like to import the data then have Project take the tasks and indent them under the Work order header.
The task number for the header row is always 00
Each task is then 010, 020, 030 etc...
I know I need to do this with VBA but I was wondering if anyone can assit with some code to get me started.

Here is a typical example of the data

045678 00 Widget change out Widget change out
045678 010 Remove Widget Widget change out
045678 020 Fix widget Widget change out
045678 030 Replace widget Widget change out
045678 040 Test widget out Widget change out

In Project I would like to see Tasks 010 - 040 indented under "Widget change out"

Thanks for reading
Regards

John

 

[PDQBach]

Label one column Outline_Level and put the, uh, outline level number there.

 

[Palmcrest]

The data has the task in column A and the header in Column B the same header for heach task.
I was more thinking that this data could be imported and sorted using code so it appears in Project with the required outline level. I didnt want to manipulate data in Excel before I did a standard import.
Not sure if Ive made that easier to understand.

Thanks

 

[PDQBach]

In your first example you have one layout; now you have a different layout.

This vba will work for your first example. It assumes that when you imported the spreadsheet into Project that column A was saved in Number1, column B in Number2, column C in Name and column D in Text1


Sub PDQBach()
Dim tsk As Task
Dim strTemp As String

strTemp = ""
For Each tsk In ActiveProject.Tasks
If Not tsk Is Nothing Then
If tsk.Text1 <> strTemp Then
strTemp = tsk.Text1
Else
tsk.OutlineLevel = 2
End If
End If
Next
End Sub

 

[Palmcrest]

Sorry that was misleading, I jumped in without filling in the blanks.
Ok Ill have a look at this,
Thankyou for your feedback
John

 

[Palmcrest]

This code works well,but I have not explained the scenario properly, My aplolgies for this. I will atempt to be more clear.
OK
Text1 will have the header data for each task
Name will have the task description data.
Rather than indent subsequent tasks under the first line which is what this code does.
I would like to create a new line and indent the tasks under the new line. The new line will have task1 data in the name field. All indented tasks will have origional text in the name field.

Example:
Name Text1
Remove valve Change out valve
Fix Valve Change out valve
Replace vavle Change out valve


Run code

Name
Change out valve Change out valve
Remove valve Change out valve
Fix Valve Change out valve
Replace vavle Change out valve

Hope thats a little more clear.
Thanks again
John

 

[PDQBach]

Sub pdqbach()
Dim boolAllDone As Boolean
Dim lngTask As Long
Dim strSaveText1 As String

SelectTaskField row:=1, Column:="Name", rowrelative:=False
EditInsert
SetTaskField Field:="Name", Value:="PDQ BACH"

lngTask = ActiveProject.Tasks.Count
strSaveText1 = ActiveProject.Tasks(lngTask).Text1
boolAllDone = False
Do
If ActiveProject.Tasks(lngTask).Text1 = strSaveText1 Then
ActiveProject.Tasks(lngTask).OutlineLevel = 2
lngTask = lngTask - 1
Else
SelectTaskField row:=lngTask + 1, Column:="Name", rowrelative:=False
EditInsert
SetTaskField Field:="Name", Value:=strSaveText1
SetTaskField Field:="Outline Level", Value:=1
strSaveText1 = ActiveProject.Tasks(lngTask).Text1
End If
Loop Until lngTask = 1

SelectTaskField row:=1, Column:="Name", rowrelative:=False
SetTaskField Field:="Name", Value:=strSaveText1
End Sub

 

[PDQBach]

Addendum

After both rows that read:

SetTaskField Field:="Name", Value:=strSaveText1

Add:

SetTaskField Field:="Text1", Value:=strSaveText1

 

[Palmcrest]

All good
Thankyou.