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

    VBA Export to Excel (2000/SR1)

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

    Can anyone advise on what I am doing wrong? I will also post this in the Excel group, as I was advised previously from this group to try that as well.

    Thanks,
    Caroline

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Export to Excel (2000/SR1)

    Caroline,

    I am a little rusty on my project VBA coding so I will take a look at this.

    We do try to refrain from making the same post across multiple boards. My previous post suggested that you "search" the excel forum, not post your question there as well.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Export to Excel (2000/SR1)

    Caroline,

    Regarding the code. Your are looking at the Proj.Resources collection and trying to get your text fields and the group field. The text fields are not part of the resource collection, but part of the task collection. This may be part of your problem. More to come.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Export to Excel (2000/SR1)

    My apologies for posting in two forums. I was under the impression that the "Search" function was no longer available; at least I've not been able to find it.

    Thanks,
    Caroline

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Export to Excel (2000/SR1)

    OK, I just found Search on the menu bar. Hadn't it been unavailable for quite some time?

    Caroline

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Export to Excel (2000/SR1)

    Caroline,

    Could you please post the rest of your code including your Dim statements.

    Your code states: For Each R....
    How will the system know what R is?
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Export to Excel (2000/SR1)

    Caroline,

    You are correct, but the search feature became available again not so long ago.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Export to Excel (2000/SR1)

    Here is the code that I've gotten to work so far. The end goal is to get timescaled data to export but I'm working it step-by-step.

    Option Explicit

    Sub Rolling_Release_Schedule()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlR As Excel.Range
    Dim Proj As Project
    Dim R As Resource
    Dim A As Assignment
    Dim T As Task
    Dim ProjectName As String
    Dim i As Integer
    Dim Tsvs As TimeScaleValues
    Dim Tsv As TimeScaleValue
    On Error Resume Next
    'Point to Excel Application
    Set xlApp = GetObject(, "Excel.Application") 'Look for running copy
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application") 'Start new instance
    If xlApp Is Nothing Then
    MsgBox "Can't Find Excel, please try again.", vbCritical
    End 'Stop, can't proceed without Excel
    End If
    xlApp.Visible = True
    End If
    'Make Excel active and set a variable to point to
    'a new workbook and cell A1 in that book.
    Application.ActivateMicrosoftApp pjMicrosoftExcel
    Set xlBook = xlApp.Workbooks.Add

    'Set workbook font size to 8
    Cells.Select
    With Selection.Font
    .Size = 8
    End With
    Range("A1").Select

    'Create report header
    Set xlR = xlBook.Worksheets(1).Range("A1")
    With xlR
    .Formula = "Title 1"
    .Font.Bold = True
    ' .Font.Size = 8
    End With
    With xlR.Range("A2")
    .Formula = "Title 2"
    .Font.Bold = True
    .Font.Italic = True
    End With
    With xlR.Range("A3")
    .Formula = "As of " & _
    Format(Date, "Short Date")
    .Font.Bold = True
    .Font.Italic = True
    End With
    With xlR.Range("A4")
    .Formula = ("***Title 3***")
    .Font.Bold = True
    .Font.Italic = True
    End With
    With xlR.Range("F4")
    .Formula = ("QAS = QA Start date QAE = QA End date GL = Go Live date")
    .Font.Bold = True
    .Font.Italic = True
    End With

    Set xlR = xlR.Range("A5")

    'Add headers and weekdates for 13 weeks
    xlR.Range("A1:C1") = Array("Planning Group", "Project", "Resource")
    With xlR.EntireRow
    .HorizontalAlignment = xlHAlignCenter
    .WrapText = True
    .Font.Bold = True
    End With

    'Format columns
    xlR.Range("A1").ColumnWidth = 15
    xlR.Range("B1:C1").ColumnWidth = 15
    xlR.Range("D1:P1").EntireColumn.NumberFormat = "mm/dd" 'Format as month/day

    'Create 13 Weekly titles. You can edit this section to output any date range you want
    'If you do change the date range be sure to edit the export of date data as well
    Dim D As Date 'Use to set date of first day of week
    Dim Dend As Date

    D = Date
    Do Until Weekday(D) = vbMonday
    D = D - 1
    Loop
    For i = 1 To 13
    xlR.Offset(0, i + 2) = CDate(D + ((i - 1) * 7))
    Next
    xlApp.Range(xlR, xlR.End(xlToRight)).NumberFormat = "mm/dd"
    Dend = CDate(D + ((i - 1) * 7))

    'Copy Data
    Set xlR = xlR.Offset(6, 0)

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: VBA Export to Excel (2000/SR1)

    Caroline,

    I have been offline for several days due to other commitments. Sorry I have not replied earlier. I will see if I can get some help from the Excel Guru's.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Export to Excel (2000/SR1)

    Thanks, I really appreciate it!

    Caroline

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Export to Excel (2000/SR1)

    This thread has been continued in this Excel thread. All further posts there, please.
    Gre

Posting Permissions

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