PDA

View Full Version : trouble copying grouped resource usage data to excel with VBA



elv!s
07-21-2009, 05:59 PM
Hi... I am new here and feel bad my first post is to ask for help.

I am taking the resource usage view and copying the sheet data over to an excel file i open with VBA. Paste it in and continue to bring the timescale data over also. That has all worked fine.

Then I thought, since I have multiple projects in this file, I will first group the resource usage data by name first (Name is the resource in the resource sheet), and then by a custom text field. I collapse the sheet to show only the second level.

The problem I get is I get an error now when running the same report. It now says "the paste method of the worksheet class failed"

Here is the code and it returns an error on the last row. I also highlighted the 2 lines that cause this to freeze. Otherwise if I comment them out it runs fine.
Help is appreciated.

Mike M.
Olrando, FL
---------------------------------------------------------------------

Global Xlsheet As Object
Global xlApp As Object
Global pjOrig As Object

Sub resource_export()

Set pjOrig = Application.ActiveProject

'select general resource table data from MS Project table
ViewApply Name:="Resource Usage"
TableApply Name:="Usage Export"
GroupApply Name:="usage Export"
OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel2

SelectSheet
EditCopy

Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"

Set xlBook = xlApp.Workbooks.Add

'paste general resource table data into MS Excel
xlApp.Range("a1").Select
xlApp.ActiveSheet.Paste

Bob Phillips
07-27-2009, 05:24 AM
Mike,

Can you post the file(s) with code so as to make it easy for us?