Exporting assignment information to Excel
David L.
get as much information as possible out of the resource usage (I am using a
shared resource pool approach between different projects) view but it seems
to be limited. Does anyone know of existing VBA code that enhances the
assignment analysis?
calihdog
Excel...". If you use this while you're in the resource usage view you will
be able to export timephased data to Excel. You are able to choose which
fields to export.
HTH.
David L.
Thanks for your help. Unfortunately the export only provides resource
information but not the assignment information (i.e. the tasks to which the
resources are assigned). How can I get this info too?
David L.
robodob
David, i too have been trying to find out how to get the resources and
assignments and hour breakdowns on one concise sheet. Usage reports will do
this for you but only in print form. WE NEED IT IN EXCEL> I will let you know
if I find anything out.
David L.
I eventually found out how to do this but I had to develop a macro to
extract the assignment information and get the data that I wanted. There was
no pre-canned query or export function that met my needs. I have another
macro that takes this data and dumps it into an Excel spreadsheet for further
processing. Let me know if you would like a copy of the code I wrote to do
this.
David.
robodob
results from the macro.
I have configured msp (I am a const. co) to track construction activities,
do payroll, change orders, unit prices, projections, budgets, billing,
analysis, (using indicators) etc.
This is the one thing I truly need. If you are interested in my template,
let me know. I can share it with you.
jlg
of time if you would share these macros. My e-mail is
082401ATroundysDOTcom.
Thank you!
Judy
ReKa
David, I too am doing something very similar and have tried to figure
it out for quite a while without any success. I would appreciate you
macro too.
My e-mail is: rek...@hotmail.com
Cheryl
I also have this need. Can you forward the macro to me as well? My email
is cl...@mindspring.com
Thanks in Advance.
Cheryl
RCR
Wallace@discussions.microsoft.com Adam Wallace
We have been trying unsuccessfully to automate the extraction of assignments
to excel for a little while. I've just stumbled across this discussion thread
and was hoping you'd be prepared to share your macro with me as well.
If you are then my email address is Adam.W...@dunlop-aerospace.com.
Thanks,
Adam
Mvisscher
schroetp
to work. If you would be willing to share yours, it would be much
appreciated.
Email is schro...@hotmail.com
THanks!
DanSmoach
roach_...@yahoo.com.au or post the code.
Thanks
Dan
DallasLDY
copy of the macro to:
Leslie....@L-3Com.com
I would really appreciate it.
Leslie
Mike Oberneder
I would also like a copy of your code, if you don't mind. Would you mind
sending me a copy at obe_...@msn.com
Thanks in advance.
Mike
DallasLDY
and asked for this information (thank you Dan - my only response).
It seems that this information is not being forwarded to anyone who has
asked. It seems that this problem has been solved in the 2007 version. If
your company is not going to upgrade to 2007, then you will have to do like
me and copy/paste the information. ugh.
Yet another disappointment.
tonyhomer
David,
Can you please forward a copy of the code, I have spent a number of
hours trying to do the same.
Regards.
Tony.
--
tonyhomer
------------------------------------------------------------------------
tonyhomer's Profile: http://forums.techarena.in/member.php?userid=22091
View this thread: http://forums.techarena.in/showthread.php?t=524030
ChuckF
JulieS
Do you have a question?
Julie
"ChuckF" <Chu...@discussions.microsoft.com> wrote in message
news:F45BE589-9CAD-4A6E...@microsoft.com...
Ann
reports. Does anyone know how to get timephased data into Excel Pivot Tables?
Thanks
Ann
JulieS
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.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project
"Ann" <A...@discussions.microsoft.com> wrote in message
news:9C0B77F4-E2A5-4899...@microsoft.com...
dtou
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
--
dtou
------------------------------------------------------------------------
dtou's Profile: http://forums.techarena.in/members/dtou.htm
View this thread: http://forums.techarena.in/microsoft-project/524030.htm
doates
Any chance one of you MS Project gurus can work a macro as David L. has done
and provide it to the community? Seeing that roughly 20 people in this
discussion thread have each individually asked David to send them the macro
code seems to me that it is something that Microsoft should offer to its
customers in the form of a technical solution. That would be HELPFUL. My
firm is a few years away from the MS Project 2007 migration, so any help for
MS Project 2003 in this regard is very appreciated.
Thanks!
JulieS
I'm not sure I follow your comment. Project 2003 still has the
"export timephased data wizard". No code development is required.
Fellow MVP has written an excellent book on VBA which includes an
entire chapter on working with timephased data. If you are serious
about developing macros to suit your needs -- I'd start there. See
http://www.projectvbabook.com
To your suggestion that one of the "gurus" provide a macro -- just
to clarify -- none of us work for Microsoft. We are not paid for
the time we spend volunteering here -- neither by Microsoft nor our
individual companies.
I personally don't have the skills to develop a macro and to be
honest, you are asking a bit much. I have no idea what David's
macro does -- I have no idea of what you would consider useful data.
If such a macro was to be developed and made available would you
expect it to work exactly for what you need? Would you then expect
someone to volunteer to re-write the code for free to suit your
needs?
You could try posting a broad requirement to the developer newsgroup
along with a method of contacting you. Perhaps someone there will
offer to create the macro for you -- but I wouldn't expect it to be
free.
Julie
"doates" <doa...@discussions.microsoft.com> wrote in message
news:62FD91D6-7C54-4384...@microsoft.com...
JulieS
Sorry that should be Rod Gil, fellow mvp....
> Fellow MVP has written an excellent book on VBA which includes an
> entire chapter on working with timephased data. If you are
> serious about developing macros to suit your needs -- I'd start
> there. See http://www.projectvbabook.com
<snip>
JulieS
See the message posted to this thread through google groups for some
code posted by "dtou"
or you can view the thread in the techarena forum:
http://forums.techarena.in/microsoft-project/524030.htm
Julie
"doates" <doa...@discussions.microsoft.com> wrote in message
news:62FD91D6-7C54-4384...@microsoft.com...
Harley Quinn
beth.burniche at xerox . com
Based on how many people wrote they need this, seems annoying that MS can't
include it on Project (well, if it's in 2007 that's cool, too bad my company
insists on still using 03)
dtou
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.
Darryl
dtou
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.
Darryl
+-------------------------------------------------------------------+
|Filename: MSPExtract v1.zip |
|Download: http://forums.techarena.in/attachment.php?attachmentid=7410|
+-------------------------------------------------------------------+
Nathalie
the same for me, I would be very interested to have this code.
my email address is : rivero...@yahoo.com
thanks a lot
Nathalie
JulieS
See the post by dtou. You can view it at:
View this thread:
http://forums.techarena.in/microsoft-project/524030.htm
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project
"Nathalie" <Nath...@discussions.microsoft.com> wrote in message
news:4C70EA86-8798-41E8...@microsoft.com...
Nathalie
thanks.
I got the macro and it worked but the code is based on the active project.
If I have five projects opened, I have a message error because I have to
change of active project to keep on executing. I wanted to add the project
name and the parent task. I added those two fields. I would like to see all
the assignments in all the projects. I have tried to modify a little bit the
code but I'm stuck.
Could you give me any help?
thanks
Nathalie
JulieS
I've not seen the macro, so I cannot advise. I suggest copying and
pasting the code into a message and posting in the project developer
newsgroup. There's a higher percentage of VBA gurus there.
I hope this helps.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project
"Nathalie" <Nath...@discussions.microsoft.com> wrote in message
news:4FC093A4-17C6-4AB1...@microsoft.com...
drjayr2002
JulieS
David's original post is from August of 2006. I'm not sure he is
even monitoring this newsgroup any longer. Also, just as a
suggestion, don't post your email address quite so broadly -- try to
change it so the email harvesters don't have such an easy time of
it.
If you have some talent with VBA, search the project developer
newsgroup (microsoft.public.project.developer) to get you started in
creating some code to export the information.
Also, have you looked at a straight export to Excel to see how close
you could get without code? If you are using Project 2007, you have
the ability to work with Project data through Excel in Visual
reports. If you are using Project 2003 or earlier, you can export
timephased data using the Analysis toolbar.
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project
"drjayr2002" <drjay...@discussions.microsoft.com> wrote in message
news:4A3FD250-8460-4253...@microsoft.com...
G28831
2007 to get what I need, but my work PC is still way behind the times.
Thanks Kindly,
Mark