Printable View
I need some help here to find out a proper way to export Microsoft Project files in Microsoft Excel. I am sure there is way to do that but I am not able to find it. There are number of resources used in that and I think in Excel I would be able to get all that I want. Is there a VBa code for that. I am sure there will be some sort of option that can help me to do the same. And going code wise can be complicated but there should be some other way also.
There is a option available in Project that can do this. You have to just find button on toolbar. The button is marked as Export Timebased data in Excel. that's all. You have to click on that and you can get a timebased sheet on the same. It is nice and works well. There are option available through which you can choose the field that are pending to export and leave which you do not need. Export works really well. There can be set of some third party software also that would work for the same process but I am doubtful on that.
There are some vb script available on web that can help you to do what you are looking for. And remember that it is safe to do that. You can simply extract a screenshot of the entire sheet but I think modification may not be allowed. The best way of doing this is running a macro. It has option to configure msp easily and works really well. I had downloaded a dummy sheet from the web that showed me number of different option to extract data from microsoft project.
Can you please forward a copy of the code, I have spent a number of hours trying to do the same.
Regards.
The Analysis Wizard has been replaced in MS Project 2007 with PivotTable
reports. Does anyone know how to get timephased data into Excel Pivot Tables?
The visual reports takes data (including timescaled) to an Excel
pivot table. See Report > Visual Reports and walk through the
process.
I hope this helps. Let us know how you get along.
After searching for something to do this without luck, I wrote some VBA to export assignment data to Excel. Here is a snippet of code I wrote to export the assignment information to Excel. Make sure a reference is added to the Microsoft Scripting Runtime and the Micrososft Excel Object Library.
I stripped out a bunch of Excel formatting code and haven't tested below to ensure it works exactly as listed but it should be very close. At a minimum, you should be able to see how the export works.
Darryl
-----------------
Option Explicit
Const REPORTING_WINDOW As Integer = 14
Sub WriteTimePhasedData()
Dim oExcel As Excel.Application
Dim objFSO As FileSystemObject
Dim dstatusdate As Date
Dim strFN As String
Dim strDir As String
Dim a As Assignment
Dim TSV As TimeScaleValues
Dim r As Resource
Dim row As Integer
Dim col As Integer
Dim i As Integer
Dim ResIndex As Integer
Dim t As Task
' Use today's date if the status date in MSP is set to "NA" otherwise use the status date.
dstatusdate = IIf(TypeName(ActiveProject.StatusDate) = "String", Now(), ActiveProject.StatusDate)
' Create blank worksheet template
Set oExcel = New Excel.Application
oExcel.Workbooks.Add
oExcel.Visible = True
strDir = "c:\project reports\" & ActiveProject.Project & "\" & Format(dstatusdate, "MM_DD_YY") & "\"
strFN = "MSP Timephased Export.xlsx"
Set objFSO = New FileSystemObject
MakeDir strDir
If objFSO.FileExists(strDir & strFN) Then
objFSO.DeleteFile strDir & strFN, True
End If
Set objFSO = Nothing
row = 1
col = 1
' Write header
oExcel.Cells(row, 1) = "Name"
oExcel.Cells(row, 2) = "Task"
oExcel.Cells(row, 3) = "Project Manager"
For i = 1 To REPORTING_WINDOW
oExcel.Cells(row, 3 + i) = " " & Format(Now() + i - 1, "MM/DD/YY")
Next
row = row + 1
' Generate reports for all resources.
For ResIndex = 1 To ActiveProject.ResourceCount
Set r = ActiveProject.Resources(ResIndex)
' Do this person have any tasks?
If r.Assignments.Count > 0 Then
' Generate the tasks for this resource.
For Each a In r.Assignments
Set TSV = a.TimeScaleData(Now(), Now() + REPORTING_WINDOW - 1, pjAssignmentTimescaledWork, pjTimescaleDays)
oExcel.Cells(row, 1) = r.Name
oExcel.Cells(row, 2) = a.TaskName
Set t = ActiveProject.Tasks(a.TaskID)
oExcel.Cells(row, 3) = t.Text1
For i = 1 To TSV.Count
If (TSV.Item(i) <> "") Then
oExcel.Cells(row, 3 + i) = Round(TSV.Item(i) / 60, 1)
End If
Next
row = row + 1
Next
End If
Next
' Save and close workbook
oExcel.ActiveWorkbook.SaveAs strDir & strFN
oExcel.ActiveWorkbook.Close
MsgBox ("Export complete. File created " & strDir & strFN)
End Sub
' Recursively create the directory path provided in fldr
' May be used with UNC paths
Private Sub MakeDir(ByVal NewFolder As String)
Dim sPath() As String
Dim FSO As FileSystemObject
Dim sFolder As String
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
sPath = Split(NewFolder, "\")
sFolder = sPath(0)
If Len(Replace(sFolder, ":", "")) = Len(sFolder) Then sFolder = "\\" & sFolder
For i = 1 To UBound(sPath)
sFolder = sFolder & "\" & sPath(i)
If Not FSO.FolderExists(sFolder) Then FSO.CreateFolder (sFolder)
Next
End Sub
How can you use the macro. I am testing that on my MS Project file from long time and till yet not able to find out that it may really work. It is a bit annoying to find a working script that really helps. I am not able to seen a set of option in macro that will really allow me to get the data as I want. I had checked that export option also. That also work in very minimum way. It is easy to get a set of timephased data, but somehow if I need to do more then what are the option available. There are some companies I had seen on web that develop macros. I want to know that does it is possible to get some solution for there.
Microsoft Project 2003 has the Export Timephased Data wizard. See another post in this thread by JulieS who describes this. It does exactly what is being asked for.
Microsoft removed the wizard functionality in MSP 2007, hence the reason for the code I wrote to extract it into Excel. I wrote a post above with some basic instructions that someone with a bit of VBA expertise should be able to use to add the macro to MSP 2007. It extracts timephased data from MSP and exports it into an Excel file suitable for analysis and formatting.
I've attached an Excel spreadsheet that exports a list of all resources and any specified assignment, task or resource fields associated with them along with the option of included time phased hours for a few time scales. I wrote it with MS Excel 2007 and MS Project 2007 type libraries. You'll likely need to update the references in the Excel file if you're using a different version of either.
There is no error handling included so if incorrect fields or data are included in the Info tab, the error messages returned won't be helpful. It hasn't been thoroughly tested but works fine for my purposes.
If you use it, drop me a line. I would be curious to see how others make use of it.
I got the macro from web and it worked. The files to export works in different ways. You have to ensure that which macro supports what kind of work. Many time in active projects this kind of problem appear and due to macro executing the entire sheet is screwed up. There is nothing you can do in it. I am stuck in exporting a sheet on the basis of plan that are over. But yet not able to get a right solution. There are third party program on the web that says it will work but there is very few chances on the same.