#1
|
|||
|
|||
Exporting Resource Usage data to Excel
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. |
#2
|
|||
|
|||
I do not work with VBA code so I'm afraid I can be of very limited help. However, instead of attempting to copy/paste you may have better success working through a direct export.
See the link below for an excerpt from Rod Gill's (Project MVP) book on Project VBA. http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx Julie |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
project 2010 resource usage | runejors | Project | 1 | 04-04-2013 05:55 AM |
MS Project - Resource usage printout | scrinmemphis | Project | 1 | 02-22-2013 02:30 PM |
Macro: Exporting Data to a LEGIBLE Excel Spreadsheet | jeffcoleky | Word VBA | 6 | 05-08-2012 08:24 AM |
* Exporting Access Data to Excel | djreyrey | Excel Programming | 1 | 03-23-2012 10:03 PM |
Resource Usage sheet - options | Andy Bain | Project | 1 | 11-05-2010 12:42 PM |