Export Data from MS Project “.MPP” files to others systems
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
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
