MS Project VBA: Trying to Paste Copied Tasks from .MPP to Excel

Question

I am trying to copy tasks from a particular filter in MS Project to an Excel document. Here is what I have so far; however, I can't get the tasks to paste to the workbook. Any assistance would be great.

    Public Sub Export_TopbarToExcel()

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim t As Task
    Dim pj As Project

    Set pj = ActiveProject
    Set xlApp = New Excel.Application

    xlApp.Visible = True
    'applies filter in project
    FilterApply Name:="TopBarReport"
    'selects filtered tasks and copies them
    SelectAll
    EditCopy

    'adds new workbook
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    'Add the project header info in the top 2 rows
    xlSheet.Cells(1, 1).Value = "Status Date"
    xlSheet.Cells(1, 2).Value = pj.StatusDate
    xlSheet.Cells(1, 3).Value = "Project Title"
    xlSheet.Cells(1, 4).Value = pj.Title
    'here is where the issue is...it is not pasting the selected info here
    xlSheet.Activate
        Range("A3").Activate
        EditPaste

    MsgBox "Done", vbInformation

    End Sub

Nothing pastes to the excel document. The Status Date and the Project Title are added and Cell A3 is selected, but nothing pastes. If I manually do a "paste special text" in excel, the information will transfer. — J Lawrence
Move the EditCopy statement further down in the code, perhaps all the activity in Excel is causing the copying process to halt. (This happens directly in Excel when something is typed in a cell.) Or add the project headers after pasting. — Andy G
Actually, EditPaste is addressing Project. It is probably just copying over-pasting the same content. You probably want xlSheet.Paste or a range.PasteSpecial. — Andy G

Andy G Andy G · Accepted Answer · 2019-03-13T14:01:21

EditPaste is a Project method so it is likely to be just copying and over-pasting the same content.

Also, activity in Excel could cause the copying process to be cancelled.

Move EditCopy further down and use xlSheet.Paste or the PasteSpecial method of a Range to get the content in Excel.

'EditCopy

'adds new workbook
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

'Add the project header info in the top 2 rows
xlSheet.Cells(1, 1).Value = "Status Date"
xlSheet.Cells(1, 2).Value = pj.StatusDate
xlSheet.Cells(1, 3).Value = "Project Title"
xlSheet.Cells(1, 4).Value = pj.Title
'here is where the issue is...it is not pasting the selected info here
xlSheet.Activate
    Range("A3").Activate

EditCopy    'happens in Project
    'EditPaste
xlSheet.Paste    'happens in Excel

Additionally, you could add the headers to Excel after pasting. The two steps are not dependent.

MS Project VBA: Trying to Paste Copied Tasks from .MPP to Excel

1 Answers