Converting from p3 to msp

Right !



A lot of Software have ActiveX-elements, which are the basics for those makros. Sometimes they are even hidden or at least don’t have any documentation.

With these ActiveX-elements you can link almost everything, either with Visual Basic to create *.exe files or with Visual Basic for Applications, which is inside MS-Programms.

Using Excel is the most easiest way, as you can store and manipulate data. System like these (DDE, OLE etc.) are available in operating systems since DOS but only used by a small group of users. Most people think its difficult to use, but as soon as you have started, you find it quit easy and find new things every day. You find a lot of support in the internet.



Here is a very simple makro for P3. You need an Excel-File with the sheets "P3-Resources" and "Success". Start Extras->Macros->Visual Basic Editor and copy this text inside. Now you have a macro called Primavera. It opens a P3-file and writes a few Data into Excel. For the rest also see the Ra.hlp - file.



Dim session As Object

Dim proj As Object

Dim act As Object

Dim res_id As Object

Dim succes As Object

Dim bret As Boolean

Dim username, password, projectname As String

Dim xRow, ActNos As Integer

Dim sheet As Workbooks



Sub Primavera()

Set session = CreateObject("P3Session")

username = Application.InputBox("Please enter Username:", "login", "debruyne")

password = ""

bret = session.Login(username, password, True)



Worksheets("P3-Resources").Cells(1, 1).Value = "ActivityID"

Worksheets("P3-Resources").Cells(1, 2).Value = "Description"

Worksheets("P3-Resources").Cells(1, 3).Value = "Resource"

Worksheets("P3-Resources").Cells(1, 4).Value = "BudgetedCost"

Worksheets("P3-Resources").Cells(1, 5).Value = "BudgetedQuantity"

projectname = Application.InputBox("Please enter ProjectName:", "project", "li01")

Set proj = session.openproject(projectname, 0, 99)

xRow = 2

ActNos = 1

For Each act In proj.activities

Worksheets("P3-Resources").Cells(xRow, 1).Value = act.ActivityID

Worksheets("P3-Resources").Cells(xRow, 2).Value = act.Description

For Each res_id In act.ResourceAssignments

Worksheets("P3-Resources").Cells(xRow, 3).Value = res_id.ResourceName

Worksheets("P3-Resources").Cells(xRow, 4).Value = res_id.BudgetedCost

Worksheets("P3-Resources").Cells(xRow, 5).Value = res_id.BudgetedQuantity

xRow = xRow + 1

Next

ActNos = nos + 1

Application.StatusBar = ("Activity: " & xRow)

Next





Worksheets("Success").Cells(1, 1).Value = "ActivityID"

Worksheets("Success").Cells(1, 2).Value = "Description"

Worksheets("Success").Cells(1, 3).Value = "Driving"

Worksheets("Success").Cells(1, 4).Value = "Successor"

Worksheets("Success").Cells(1, 5).Value = "Lag"

Worksheets("Success").Cells(1, 6).Value = "Type"

xRow = 2

ActNos = 1

For Each act In proj.activities

Worksheets("Success").Cells(xRow, 1).Value = act.ActivityID

Worksheets("Success").Cells(xRow, 2).Value = act.Description





For Each succes In act.Successors

Worksheets("Success").Cells(xRow, 3).Value = succes.IsDriving

           Worksheets("Success").Cells(xRow, 4).Value = succes.SuccessorActivityId

Worksheets("Success").Cells(xRow, 5).Value = succes.RelationShipLag

Worksheets("Success").Cells(xRow, 6).Value = succes.RelationShipType



xRow = xRow + 1

Next

ActNos = nos + 1

Application.StatusBar = ("Activity: " & xRow)

Next



session.closeproject proj

End Sub







Hannes de Bruyne

I understand from this tread that Ra is basically used to enable macros (excel for example) to directly modify P3 data tables. is this true?



something like an API???



thus Activity.xls is using this Ra?

(looks like I need to reread the manuals).



but It seems obvious to me that there is a lot more to it than found in the manuals (in the CD). people keep poping up with new code like from nowhere.



What is the background of research to accumulate to spurt this kind of stuff?



Gün.

Hello Sean,



MSP and P3 actualy work with the same system, both can have calendars and have a main calendar. In my complete programm, I also developed a system to transfer these calenders and the calendar-ID.

The reason why I did not build in this transfer yet, is that I hoped to get some new ideas in this forum.



Hannes de Bruyne

Be very careful when transfering data from P3 to MSP and Vica Versa the MSP does not handle this tranfer very well.

You query focuses on how codes tranfers when equally important is the fact that calendar related data does not transfer accurately.

Any activity with a lag on will use the standard (project calendar) in MSP to reschedule your data.

This may be O.K if your original P3 project has a five day calendar as standard however if you have multiple calendars in P3 say a mix of five day working calendars and 7 day working then MSP will default to using the five day calendar to calculate lags. MSP is not able to handle the transfer too well. The best thing is to leave things as they are as P3 is a far superior planning package.

But if you feel you must go down this route then look further than coding as other data may not be the same.

Hannes,



Thanks for the idea.

Obviously it needs some knowledge about macros.



What is good in it is the opening of the complete field you showed.



Best regards

Wadgassen - Germany.



JMFrade

(Ok, this is converting from MSProject to P3 - I think this is the more important direction - but it shows how to do it. I also created a macro the other way around. If you are interested let me know)



I wonder, no reactions on my macro – it was read, but no answers. Although the idea is very good, maybe it does not work on other computers or people are not so familiar with macros as I thought they are. Maybe the whole thing needs a little more explanation:



What you need is:



•     P3 together with Ra – as Ra is installed with P3 this should be no question.

•     A P3 – File, that can be overwritten (Call it something like MS00)

•     Microsoft Project or at least the file MSPRJ.OLB in the directory c:\programms\microsoft office\office 10

•     A MS_project file with activities, resources and everything (Call it something like Test.mpp)

•     Excel

•     An Excel file (Call it translate.xls). This file should have the sheets: Activity, Resource, WBS-Dictionary, Activity-Code, Filters, CDI and Calendar. (some of them are not used yet, but are for future extension)

•     A macro. Mark the macro text from the example and copy it (CTRL+C). In the excel file go to the menu Extras -> Macro -> Macros. The Macro-name should be “fromProjectToP3”. You’r in the Visual Basic editor now and should read something like :



Sub FromProjectToP3()

End Sub



This text must be marked, as it must be overwritten. Paste the text you have copied (see 8 lines before). (CTRL+P). Take care, it has overwritten the 2 lines. Now you have a macro, but it does not work yet. The file MSPRJ.OLB, which is the MS-Project equivalent to Ra is still missing. As you are still in the Visual Basic editor go to the Menu Extras -> Reference (I think this line is called reference, but as I use a German version I’m not quit sure – at least it is the first line). Now search for “Microsoft Project 10.0 Object Library” and mark this line. Now the whole thing should work. Leave Microsoft Visual Basic by clicking the Excel Icon (Top Left in the menu-line of this window) .

You can start the macro now Extras->Macro->Macros->fromMStoP3->run (or is it execute?).

Select a MSProject-File and you will see it fills the Excel-sheet. The second part asks far username and –file for P3 and writes into P3.



One small error is in the macro ’Call ReadMSPjroject should NOT have this mark before Call. I used it for testing.



The part with the links is not yet in the macro, it will come next week.







I could have written this macro in MS-Visual Basic, make an *.exe file out of it and sell it, but I don’t like the Idea. I rather make it an open-source code, and add the improvements, reaction and proposals to it. So do ask questions, write me if errors occur,



Furthermore I think Ra with Excel-Macros can solve a lot of other problems with Primavera e.g. P3 is not restricted to just one WBS-Code-Structure. Or you can link P3 and AutoCad! Many of the questions I read in this forum can be so easily solved with small macros. A forum is not just for questions and answers it also needs new ideas.



I hope this is a first step for many people in the macro- world







Hannes de Bruyne

Hi,



I think, the MPX is a very poor tool! The nicest way to convert is with help of RA!

Just write a small macro in Excel to read from MS-Project and to write into P3. If you are interested I can help you with the first steps. I don’t want to send the whole macro, as I would like to see if new ideas arrise, but I’m pleased to help with basics and support in this forum.



You will need an Excel-File with a sheet called "Activity" for this:



Option Explicit



Dim MSProj, ProjAppl As Object

Dim A As Assignment



Dim strName, TaskName, TaskSuccessor, Title As String

Dim n As Long

Dim bRet, Down, DoOff, FrOff, SaOff, SoOff As Boolean

Dim iColumn, iEmpty As Integer

Dim Cal As Calendar

Dim Fil As Filter

Dim myRange As Range



’Primavera P3

Dim Pos1, Pos2, Pos3, Pos4 As Long

Dim UserName, Password, ProjectName, SuccHelp, WBSstring, fname As String

Dim iRow, i, j, l, temp, WBS(7), LastLevel As Integer

Dim Session, P3Proj, Act, Log, Succ, Res, P3Cal, P3GloCal, ProjAbstr, ResAssign As Object

Dim HoDay, Level, ResLimit, ACodes, CodeVal, CDI, P3Filter As Object



Sub fromMStoP3()

’Call ReadMSPjroject

Call WriteP3

End Sub



Function ReadMSPjroject()

Set MSProj = CreateObject("MSProject.Project")

Set ProjAppl = MSProj.Application



Title = "Please Select Microsoft Project File"

fname = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp", , Title)





ProjAppl.FileOpen fname, ReadOnly:=False, FormatID:="MSProject.MPP"

strName = ProjAppl.ActiveProject.Name

Application.DisplayStatusBar = True







’****************************** ACTIVITIES ***************************************



Worksheets("Activity").Activate

Cells.Select

Selection.ClearContents

Cells(1, 16) = "ProjStart:"

Cells(1, 17) = ActiveProject.ProjectStart

Cells(1, 18) = "ProjFinish:"

Cells(1, 19) = ActiveProject.ProjectFinish

Cells(1, 20) = "StatusDate:"

Cells(1, 21) = ActiveProject.StatusDate

Cells(1, 22) = "Version:"

Cells(1, 23) = ActiveProject.VersionName

Cells(1, 24) = "Digits:"

Cells(1, 25) = ActiveProject.CurrencyDigits

Cells(1, 26) = "StartWeekOn:"

Cells(1, 27) = ActiveProject.StartWeekOn





Cells(1, 1) = "Activity"

Cells(1, 6) = "Headers"

Cells(1, 11) = "Constraints"

Cells(1, 13) = "Actual"



Cells(2, 1) = "ID"

Cells(2, 2) = "NAME"

Cells(2, 3) = "OD"

Cells(2, 4) = "Successors"

Cells(2, 5) = "Milestone"

Cells(2, 6) = "Summary"

Cells(2, 7) = "Number"

Cells(2, 8) = "Level"

Cells(2, 9) = "PSP"

Cells(2, 10) = "Note"

Cells(2, 11) = "Date"

Cells(2, 12) = "Type"

Cells(2, 13) = "Actual Start"

Cells(2, 14) = "Actual Finish"

Cells(2, 15) = "Percent Compl."

Cells(2, 16) = "Calendar"

Cells(2, 17) = "Text1"

Cells(2, 21) = "Resources"





For i = 1 To ActiveProject.Tasks.Count

Application.StatusBar = "Reading activity " & i & " of " & ActiveProject.Tasks.Count

If Not ActiveProject.Tasks(i) Is Nothing Then



Cells(i + 2 - iEmpty, 1) = ActiveProject.Tasks(i).ID

Cells(i + 2 - iEmpty, 2) = ActiveProject.Tasks(i).Name

Cells(i + 2 - iEmpty, 3) = ActiveProject.Tasks(i).Duration / (ActiveProject.HoursPerDay * 60)

Cells(i + 2 - iEmpty, 4) = ActiveProject.Tasks(i).Successors

Cells(i + 2 - iEmpty, 5) = ActiveProject.Tasks(i).Milestone

Cells(i + 2 - iEmpty, 6) = ActiveProject.Tasks(i).Summary





Cells(i + 2 - iEmpty, 10) = ActiveProject.Tasks(i).Notes

Cells(i + 2 - iEmpty, 11) = ActiveProject.Tasks(i).ConstraintDate

Cells(i + 2 - iEmpty, 12) = ActiveProject.Tasks(i).ConstraintType

Cells(i + 2 - iEmpty, 13) = ActiveProject.Tasks(i).ActualStart

Cells(i + 2 - iEmpty, 14) = ActiveProject.Tasks(i).ActualFinish

Cells(i + 2 - iEmpty, 15) = ActiveProject.Tasks(i).PercentComplete

Cells(i + 2 - iEmpty, 16) = ActiveProject.Tasks(i).Calendar

Cells(i + 2 - iEmpty, 17) = ActiveProject.Tasks(i).Text1

Cells(i + 2 - iEmpty, 18) = ActiveProject.Tasks(i).Text2





Else

iEmpty = iEmpty + 1

End If

Next i

Exit Function



NoFileOpen:

’MsgBox MSG_NOFILEOPEN, vbExclamation + R_TO_L, Title:=Application.Name

End ’Makro beenden.



End Function



Function WriteP3()

Set Session = CreateObject("P3Session")

UserName = Application.InputBox("Please enter Username:", "P3-Login", "debruyne")

Password = ""

bRet = Session.Login(UserName, Password, True)

ProjectName = Application.InputBox("Please enter ProjectName:", "P3-Project", "ms00")

Set P3Proj = Session.Openproject(ProjectName, 0, 99)





Application.DisplayStatusBar = True



Application.StatusBar = "Deleting Activities..."

For i = 1 To P3Proj.Activities.Count

bRet = P3Proj.Activities.Remove(P3Proj.Activities.Item(P3Proj.Activities.newcursor().getfirst().ActivityID))

Next



Application.StatusBar = "Deleting Resources..."

For i = 1 To P3Proj.resources.Count

bRet = P3Proj.resources.Remove(P3Proj.resources.Item(1))

Next



Application.StatusBar = "Deleting Calendars..."

For i = 1 To P3Proj.GlobalCalendar.Holidays.Count

bRet = P3Proj.GlobalCalendar.Holidays.Remove(P3Proj.GlobalCalendar.Holidays.Item(1))

Next i



For i = 1 To P3Proj.Calendars.Item(1).Holidays.Count

bRet = P3Proj.Calendars.Item(1).Holidays.Remove(P3Proj.Calendars.Item(1).Holidays.Item(1))

Next i



For i = 1 To P3Proj.Calendars.Count - 1

bRet = P3Proj.Calendars.Remove(P3Proj.Calendars.Item(2))

Next







’******************************** ACTIVITIES ***************************



iRow = 3

Worksheets("Activity").Activate



Do While Cells(iRow, 1) <> ""



Application.StatusBar = "Adding activity " & Cells(iRow, 1)



Set Act = P3Proj.Activities.Newitem() ’Prepare for new activity’s data

Act.ActivityID = CStr(Cells(iRow, 1)) ’Set ID to worksheet value

Act.Description = Left(Cells(iRow, 2).Text, 48) ’Set Title to worksheet value

Act.OriginalDuration = Cells(iRow, 3) ’Set OD to worksheet value

If Cells(iRow, 5) = True Then

Act.ActivityType = 3 ’Start Milesone

If Cells(iRow, 12) = 6 Then Act.ActivityType = 4 ’finish Milestone

End If

If Cells(iRow, 6).Text = True Then Act.ActivityType = 6







If Cells(iRow, 12) > 0 Then

temp = Cells(iRow, 12)

Select Case temp

Case 1

Act.FloatConstraintType = 9

Case 2

Act.MandatoryConstraintType = 5

Act.MandatoryConstraintDate = CDate(Cells(iRow, 11))

Case 3

Act.MandatoryConstraintType = 6

Act.MandatoryConstraintDate = CDate(Cells(iRow, 11))

Case 4

Act.EarlyConstraintType = 1

Act.EarlyConstraintDate = CDate(Cells(iRow, 11))

Case 6

Act.EarlyConstraintType = 2

Act.EarlyConstraintDate = CDate(Cells(iRow, 11))

End Select

End If

If IsDate(Cells(iRow, 13)) Then Act.ActualStart = CDate(Cells(iRow, 13))



If IsDate(Cells(iRow, 14)) Then Act.ActualFinish = CDate(Cells(iRow, 14))



If Cells(iRow, 15) > 0 Then

Act.PercentComplete = Cells(iRow, 15).Text ’Set AF to worksheet value

’act.RemainingDuration = act.OriginalDuration - act.PercentComplete * act.OriginalDuration

End If





bRet = P3Proj.Activities.Add(Act) ’Add the new activity info to the database







’*************************** ACTIVITY.LOGS ******************************



iColumn = 10

If Cells(iRow, iColumn).Text <> "" Then

l = Len(Cells(iRow, iColumn).Text)

Pos1 = 1

i = 1



Do While Pos1 < l + 1 And Pos1 <> 0

Pos2 = InStr(Pos1 + 1, Cells(iRow, iColumn).Text, Chr(13))

Set Log = Act.ActivityLogs.Newitem()

Log.SequenceNumber = i

If Pos2 = 0 Then

Pos2 = l + 1

End If

Log.Text = Left(Mid(Cells(iRow, iColumn).Text, Pos1 + IIf(Pos1 = 1, 0, 1), Pos2 - Pos1 + IIf(Pos1 = 1, 0, -1)), 48)





bRet = Act.ActivityLogs.Add(Log)

i = i + 1

Pos1 = InStr(Pos1 + 1, Cells(iRow, iColumn).Text, Chr(13))

Loop

End If

Pos1 = 1

iRow = iRow + 1

Loop



P3Proj.SchedulingOptions.ListConstraints = True

P3Proj.SchedulingOptions.ListOpenEnds = True

P3Proj.SchedulingOptions.ListOutOfSequenceProgress = True



’P3Proj.SchedulingOptions.UseProgressOverride = False

Call P3Proj.ExecuteSchedule



Session.closeproject P3Proj

Session.logout

End Function







Regards

Hannes de Bruyne

Good Morning Planner Planner



I am glad I could help. Where are you. What is the time their. Can’t be in New York. Posted at New York Time 4 am. As per my Calc.



Cheers

You do realise this generated autamtically in MS when you MPX Convert it. You dont have to do it manualy.

Text10 = Old Act ID



Cheers

OK



Insert the Text Field Column in MS Project. (In These you should see the (Old Code) Not Code Descriptions.



Then you need to sort these by the (Codes) Copy & Paste the Description Over the Code (Or in Next Text Field)



Then You Sort as I have explained.



I Hope this helps

Hi



I must ask you something



Have you converted the p 3 file to MPX ?



Once you have opened the MPX file in MS did you look what is in the text fields ?.



Cheers

I have the experience of converting MPP to P3.



save the MPP as MDB file,then select the data and save as dbf,make sure the field length is same as in P3



then import into p3,you may do this several times to import all the data you want.



I have not tried P3 to MPP, I think it can be done by import/export



if you are to do this once a week or more often. You’d better write some code to do it.

Project support VBA, so you can write some code in Project and using RA to read P3 data directly into Project

Good Day



Try This.



Use MPX Conversion (P3 to MPX)



Open up in MP



Go Project - Group By - More Groups - New - Field Name Select - Text 1 (Your P3 Codes go into MS project text Fields)



Once you have Organised Changed the Code to your P3 Code Description (Type & Copy Works)



Now you should have the same Layout as in P3.



But as to the Data I dont Know what got Lost

MSP generates automatic WBS that almost of no use. And there is no code structure in MSP but if you want to group or organise the activites you have to create custom fields but only for limited use.