Results 1 to 8 of 8

Thread: Exporting assignment information to Excel

  1. #1
    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.

  2. #2
    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.

  3. #3
    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.

  4. #4
    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.

  5. #5
    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

  6. #6
    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.

  7. #7
    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.

  8. #8
    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.

Similar Threads

  1. Exporting Project files in form of Excel Sheet
    By aelmalki in forum Microsoft Project
    Replies: 10
    Last Post: 20-04-2011, 07:49 PM
  2. Replies: 2
    Last Post: 15-04-2010, 02:57 AM
  3. Exporting datatable to excel with ADO.net
    By Sanket07 in forum Software Development
    Replies: 2
    Last Post: 03-02-2009, 05:52 PM
  4. Replies: 3
    Last Post: 15-02-2008, 02:45 AM
  5. Replies: 3
    Last Post: 02-11-2006, 06:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,619,140.81255 seconds with 17 queries