MS Project to Excel Export steps through fine but fails on run RRS feed

  • Question

  • The following code I have will run just fun if I step through (or even Run to Cursor at a few select points) but when I run it all the way through I get Run-time error '5': Invalid procedure call or argument.

    I've picked through it several times but I do not know what I am missing.  I am writing this in MS Project with the plan of populating an Excel sheet to then output a text file that will fill in the boxes on a PDF sheet.  Excel > text > PDF is so far going fine, but getting the data out of Project and into the correct boxes is proving to be my issue.

    Public Sub portalToExcel()
        Dim excelApp As Excel.Application
        Dim excelBook As Excel.Workbook
        Dim excelSheet As Excel.Worksheet
        Dim proj As Project
        Dim t As Task
        Dim pj As Project
        Set pj = ActiveProject
        Set excelApp = New Excel.Application
        excelApp.Visible = True
        AppActivate "Excel"
        Set excelBook = excelApp.Workbooks.Open("C:\Users\jv\Documents\Applications Development\Project Plan\Testing\SampleSheet.xlsx")
        Set excelSheet = excelBook.Worksheets("Sheet1")
        excelSheet.Cells(1, 1).Value = pj.Name
        For Each t In pj.Tasks
            excelSheet.Cells(t.ID + 1, 2).Value = t.ID
        Next t
    End Sub

    Here is my reference priority order, in case it helps:

    1. Visual Basic for Applications
    2. Microsoft Project . Object Library
    3. Microsoft Excel 16.0 Object Library
    4. OLE Automation
    5. Microsoft Office 16.0 Object Library



    Monday, May 14, 2018 9:37 PM


  • Well in case anyone else ever has this problem I figured out a solution.  The hangup was happening at AppActivate "Excel" and or AppActivate "Microsoft Excel" so to get around this I replaced that code with the following:

    On Error Resume Next
    AppActivate "Microsoft Excel"
    AppActivate "Excel"

    Everything seems to be working fine now, however if anyone comes up with a more elegant solution please do post here.

    • Marked as answer by J-Vick Tuesday, May 15, 2018 8:22 PM
    Tuesday, May 15, 2018 8:22 PM