Export Data from MS Project “.MPP” files to others systems

Export Data from MS Project “.MPP” files to others systems

project

Integration Tecnology Available
– OLEDB
– ADO
– XML
– C/SIDE NAV (Oledb .NET Integration)
– SQL IIS using OLE DB Data Source\Destination > Project fields Mapping
– Project Server (Integrated SQL Technology) > Best Practice for my opinion!

… some links about exporting data using ADO and OLE DB Provider

project 2

OLEDB
Microsoft Project OLE DB Provider Information

This document is intended to provide the information necessary to access Microsoft Project
data through the Microsoft Project 9.0 OLE DB Provider.
In addition to describing the OLE DB table structures,
this document details some of the limitations and idiosyncrasies of the provider,
how to access the table structure using data access pages, and sample ActiveX Data Objects (ADO) code.

link http://www.msproject.com/OLEDB.htm

ADO

Export MS Project Data to Excel
Here is a simple routine to export most MS Project task information to Excel.
Due to the 256 column limit of ODBC, the limit to the number of exported columns is 256

Function ProjectToExcel2007()
Dim cnnProj As New ADODB.Connection ‘Ado connection to ms project
Dim rs As New ADODB.Recordset ‘Project tasks table

cnnProj.Open “Provider = Microsoft.Project.OLEDB.11.0; Project Name =” & ActiveProject.Path & “\” & ActiveProject.Name
rs.Open “SELECT * FROM Tasks”, cnnProj, adOpenKeyset, adLockOptimistic
‘If InStr(0, ActiveProject.Path & “\” & ActiveProject.Name, “.mpp”) > 0 Then
Dim objXL As New Excel.Application
objXL.EnableEvents = False

Dim objWkb As New Excel.Workbook
Dim objSht As Worksheet
Dim j, colIndex As Integer
Dim rng As Range
‘DoCmd.Hourglass True

Set objWkb = objXL.Workbooks.Add

‘trick to enable Excel 2007 ability to have morethan 255 columns (save as xlsx and repoen)

objWkb.SaveAs TempFolder() & “tmp.xlsx”
objWkb.Close
objXL.Workbooks.Open TempFolder() & “tmp.xlsx”

Set objWkb = objXL.ActiveWorkbook
Set objSht = objWkb.ActiveSheet
With objSht
.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
.PageSetup.CenterHeader = “My Report Header”
.PageSetup.CenterFooter = “My report footer”
.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
.PageSetup.BlackAndWhite = False
.PageSetup.Zoom = False
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 500
.Rows(1).Font.Bold = True
.Rows(1).Borders.ColorIndex = 1
.Rows(1).Interior.ColorIndex = 45
.Rows(1).HorizontalAlignment = xlCenter
.Rows(1).Borders.Color = vbBlack
.Rows().NumberFormat = “@”
‘ Freeze Window
.Application.ActiveWindow.SplitRow = 1
.Application.ActiveWindow.FreezePanes = True

End With

objXL.Visible = True
For colIndex = 0 To rs.Fields.Count – 1
objSht.Cells(1, colIndex + 1) = rs.Fields(colIndex).Name
objSht.Cells(1, colIndex + 1).EntireColumn.AutoFit
objSht.Cells(1, colIndex + 1).NumberFormat = “@”
Next

‘Transfer the data to Excel
objSht.Range(“A2″).CopyFromRecordset rs

‘End If

If rs.State = 1 Then
rs.Close
End If
‘Blank out all Ado null date values
For Each objSht In objWkb.Worksheets
objSht.Cells.Replace What:=”1/0/1900″, Replacement:=””, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

End Function

Function TempFolder() As String
Dim objFSO, objFolder
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.GetSpecialFolder(2)

TempFolder = objFolder.Path & “\”
End Function

link https://officetechsupport.wordpress.com/2010/05/21/export-ms-project-data-to-excel/

ADO connecting to a .mpp file or similar – Microsoft Project Developer
make a direct ADO connection to a .xls file

Sub ConnectLocally()
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
Dim FILE_NAME As String

FILE_NAME = “C:\Mes Doents\MS Project 2K\StockProjets\Projet B.mpp”

conData.ConnectionString = “Provider=Microsoft.Project.OLEDB.9.0;PROJECT
NAME=” & FILE_NAME
‘ To connect to a Microsoft SQL Server or Oracle Server file, you must also
supply User ID and Password arguments
‘ conData.ConnectionString = “Provider=Microsoft.Project.OLEDB.9.0;User
ID=jsmith;Password=MyPass5;PROJECT NAME=” & FILE_NAME

conData.ConnectionTimeout = 30
conData.Open

strSelect = “SELECT ResourceUniqueID, AssignmentResourceID,
AssignmentResourceName, TaskUniqueID, AssignmentTaskID, AssignmentTaskName
FROM Assignments WHERE TaskUniqueID > 0 ORDER BY AssignmentTaskID ASC”
rstAssigns.Open strSelect, conData

Do While Not rstAssigns.EOF
For intCount = 0 To rstAssigns.Fields.Count – 1
strResults = strResults & “‘” & rstAssigns.Fields(intCount).Name
& “‘” & _
Space(30 – Len(rstAssigns.Fields(intCount).Name)) & vbTab &
_
CStr(rstAssigns.Fields(intCount).Value) & vbCrLf
Next
strResults = strResults & vbCrLf
rstAssigns.MoveNext
Loop

conData.Close

Open “C:\Mes Doents\Results.txt” For Output As #1
Print #1, strResults
Close #1

Shell “Notepad C:\Mes Doents\Results.txt”, vbMaximizedFocus

End Sub

XML
Project “SaveAS” .XML file
Saving and Opening Projects in XML Format
link https://msdn.microsoft.com/en-us/library/bb968621(v=office.12).aspx

The Microsoft Office Project 2007 XML Data Interchange Schema (mspdi_pj12.xsd) defines how Project 2007 reads and writes project data in XML format. This article describes how to work with Project 2007 XML Data Interchange files, and contains the following sections:

– Saving Project Files as XML
– Opening Project XML Files
.. we can use NAV XML Ports to import\export data in NAV reading from XML saved from Project

C/SIDE NAV
.. example of integration between NAV & Project using C/SIDE

Automations Objects
‘Microsoft Scripting Runtime’.FileSystemObject
“Automation Your MS Project Release”.Project
“Automation Your MS Project Release”.Tasks
“Automation Your MS Project Release”..Resource
“Automation Your MS Project Release”.TaskDependencies

Tables
TblJobNAV  table  Job
tblJobTasks tabel Job Task
tblJobTasksResource table Job Planning Line

Variabiles
prjPrj
prjApp
prjTasks
prjDependiences
prjResources
…..

Function SendTasksToProject
WITH JobTask DO BEGIN
CREATE(prjApp);
FileExist:=FALSE;
IF KeepFileCopy THEN BEGIN
File_Name:=FileName;
IF EXISTS(FileName) THEN BEGIN
prjApp.FileOpenEx(FileName);
prjPrj :=  prjApp.ActiveProject;
FileExist:=TRUE;
END;
END ELSE
IF EXISTS(FileName) THEN ERASE(FileName);
…..
…..

IF NOT FileExist THEN BEGIN
prjApp.FileNew;
prjPrj :=  prjApp.ActiveProject;
prjPrj.”Custom Job No.”:= Rec.”No.”;

JobTask.SETRANGE(“Job No.”,Rec.”No.”);
JobTask.SETFILTER(“Job Task Type”,'<>%1&<>%2′,”Job Task Type”::Total,”Job Task Type”::”End-Total”);
IF FINDFIRST THEN BEGIN
k:=0;
REPEAT
k+=1;
Wnd.UPDATE(1,ROUND(k/JobTask.COUNT*5000,1));
AddTask(JobTask,prjApp);
UNTIL NEXT=0;
END;
End Function

Function ReceiceTasksFromProject
JobTask.INSERT;
prjTask.”Custom Job No.”:=JobTask.”Job No.”;
prjTask.”Custom Job Task No.” :=JobTask.”Job Task No.”;

IF prjTask.OutlineChildren.Count()<>0 THEN BEGIN // Internal Project function
JobTask.”Job Task Type”:=JobTask.”Job Task Type”::”Begin-Total”;
JobTask.MODIFY;
JobTask.”Job Task No.”:=PADSTR(COPYSTR(JobTask.”Job Task No.”,1,(prjTask.OutlineLevel)*2),MaxOutlineLevel*2,’9′);
JobTask.”Job Task Type”:=JobTask.”Job Task Type”::”End-Total”;
JobTask.Description:=COPYSTR(Text005 + ‘ ‘ + prjTask.Name,1,MAXSTRLEN(JobTask.Description));
JobTask.Indentation:=prjTask.OutlineLevel-1;
JobTask.”Starting Date”:=prjTask.Start;
JobTask.”Ending Date”:=prjTask.Finish;

IF NOT JobTask.INSERT THEN;
….
….
….
End Function

Function AddTask(VAR JobTask: record:”Job Task”, PrjApp:Project Automation.”Application”)
WITH JobTask DO BEGIN
prjPrj:= PrjApp.ActiveProject;
prjTask := prjPrj.Tasks.Add(Description);
prjTask.”Custom Job No.”:=JobTask.”Job No.”;
prjTask.”Custom Job Task No.” :=JobTask.”Job Task No.”;

IF “Starting Date”<>0D THEN
prjTask.Start    := (CREATEDATETIME(“Starting Date”,”Starting Time”));
IF “Ending Date”<>0D THEN
prjTask.Finish   := (CREATEDATETIME(“Ending Date”,”Ending Time”));

IF “Due Date”<>0D THEN
prjTask.Deadline := (CREATEDATETIME(“Due Date”,”Due Time”));

….
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.