Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Houston, Texas, USA
    Thanked 0 Times in 0 Posts

    MS Project VBA Export to Excel (2000/9.0.6926 SP-3)

    I am writing a program to export timephased data from one MS Project plan to Excel. I already have a copy of "Exporting Report Data to Excel - Parts 1,2,3" from Woody's Watch and I have the Pyron book. So far I am able to write my header and column information to Excel from inside Project. However, when I try to write actual data, nothing happens. I am trying to write three fields (before writing the timephased data), consisting of two custom text fields and the Resource Group field. I've been working with variations of the following code:

    Set Proj = ActiveProject
    Set xlR = xlR.Range("A7")
    For Each R In Proj.Resources
    xlR.Range("A1:C1") = Array(T.Text15, T.Text7, R.Group)
    Set xlR = xlR.Offset(1, 0)

    Can anyone advise on what I am doing wrong? I have also posted this in the MS Project group; they advised me previously to try this group as well.


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: MS Project VBA Export to Excel (2000/9.0.6926 SP-3)

    To "dump" an array into excel requires a 2-dimensional array of the same size as the range. You can't use ARRAY function since it creates a 1-dim array. Something along the lines of this (if you want array route)

    Dim varray(1 To 1, 1 To 3)
    set xlr = xlr.resize(1,3)
    varray(1, 1) = T.Text15
    varray(1, 2) = T.Text7
    varray(1, 3) = R.Group
    xlr.Range("A1:C1") = varray

    Or more direct (and a little slower route):
    set xlr = xlr.resize(1,3)
    xlR.Range("A1") = T.Text15
    xlR.Range("B1") = T.Text7
    xlR.Range("C1") = R.Group


Posting Permissions

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