Consulting

Results 1 to 2 of 2

Thread: trouble copying grouped resource usage data to excel with VBA

  1. #1
    VBAX Newbie
    Joined
    Jul 2009
    Posts
    2
    Location

    trouble copying grouped resource usage data to excel with VBA

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    Mike,

    Can you post the file(s) with code so as to make it easy for us?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •