Go Back   TechArena Community > Technical Support > Computer Help > Office Help > Microsoft Project
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Exporting assignment information to Excel

Microsoft Project


Reply
 
Thread Tools Search this Thread
  #1  
Old 05-06-2006
Member
 
Join Date: Nov 2005
Posts: 32
Exporting assignment information to Excel

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.

Reply With Quote
  #2  
Old 13-08-2006
Member
 
Join Date: Oct 2004
Posts: 33
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.
Reply With Quote
  #3  
Old 10-08-2007
Member
 
Join Date: Oct 2005
Posts: 42
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.
Reply With Quote
  #4  
Old 12-06-2008
Member
 
Join Date: Feb 2007
Location: uk
Posts: 1
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.
Reply With Quote
  #5  
Old 30-10-2008
Member
 
Join Date: Oct 2008
Posts: 2
Re: Exporting assignment information to Excel

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
Reply With Quote
  #6  
Old 25-11-2008
Member
 
Join Date: Oct 2005
Posts: 39
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.
Reply With Quote
  #7  
Old 26-11-2008
Member
 
Join Date: Oct 2008
Posts: 2
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.
Reply With Quote
  #8  
Old 06-01-2010
Member
 
Join Date: Oct 2005
Posts: 39
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.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > Microsoft Project
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Exporting assignment information to Excel"
Thread Thread Starter Forum Replies Last Post
Exporting Project files in form of Excel Sheet aelmalki Microsoft Project 10 20-04-2011 07:49 PM
Remove Time from Start/Finish Date Columns When Exporting to Excel Cic!cone Microsoft Project 2 15-04-2010 02:57 AM
Exporting datatable to excel with ADO.net Sanket07 Software Development 2 03-02-2009 05:52 PM
Exporting project information to excel pivot table and workbook Itsme Microsoft Project 3 15-02-2008 02:45 AM
Windows Media Player 11 Exporting wmdb library to excel/access look at tags Omar Media Player 3 02-11-2006 06:35 PM


All times are GMT +5.5. The time now is 03:57 PM.