Home › Forums › Discussion Forum › Exporting resource Usage view to Excel
Tagged: Excel 2007, Microsoft Project 2007
Hi,
I am trying to export data from the resource usage view in microsoft project into excel.
I am using Microsoft Project 2007 and Excel 2007.
I can export the left hand side of the resource usage screen, but i cannot export the time phased values in the right hand side of the screen.
I have the code below which runs in Project, and was wondering if anyone could help me with this
The code below exports the left hand side of the screen without any problems, however the data on the right hand side does not export.
##########
Sub Export_to_Excel()
‘Variables
Dim xlRange as Excel.Range
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
‘Open Excel
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate “Microsoft Excel”
Set xlWkb = xlApp.Workbooks.Add
‘###################################
‘EXPORTS RESOURCE USAGE – FIRST HALF
‘###################################
‘Copy first half of resource usage data
ViewApply Name:=”Resource Usage”
SelectResourceColumn Column:=”Name”, Additional:=1
OutlineShowAllTasks
EditCopy
‘Selects where to add new data
Set xlSheet = xlWkb.Worksheets(“Sheet1”)
xlSheet.Activate
Set xlRange = xlSheet.Range(“A2:A2″)
xlRange.Select
‘Paste data into Excel
xlRange.PasteSpecial Paste:=xlPasteValues
‘###################################
‘EXPORTS RESOURCE USAGE – SECOND HALF – not working, need to resolve
‘###################################
‘Copy second half of resource usage data
ViewApply Name:=”Resource Usage”
PaneNext
SelectTimescaleRange Row:=1, StartTime:=”Mon 29/07/13 00:00″, Width:=13, Height:=10
EditCopy
‘Selects where to add new data
Set xlSheet = xlWkb.Worksheets(“Sheet1”)
xlSheet.Activate
Set xlRange = xlSheet.Range(“G2:G2”)
xlRange.Select
‘Paste data into Excel
xlRange.PasteSpecial Paste:=xlPasteValues
End Sub
##########
NOTE: I have already turned on the VBA project reference ‘Microsoft Excel 12.0 Object Library’.
Thanks in advance for any help.
Sam.
Sam,
why don’t you create a visual report for Resource Usage data (make sure you set the depth to days)?
That way you’ll create a pivot table with all the information you will find in the Resource Usage sheet.
Best of luck,
Ben Wiegers
Hi.
I am trying to save a view in ms project into excel.
i want resource name form into excel. how do I save a view into excel???