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