Moving notes field from Project to Excel

[alpha125]

Hi all,

I am using some code I found on the internet to move my task data from MS project standard 2010 to excel. I have given the code below. My issue is that in the notes field the newline isnt preserved. I am looking for a line or two of code that will help preserve the carriage returns. I have given an example below. Any help would be much appreciated.

Example:

In project if I enter the following note:

This

is

a

world



Then the text gets printed to excel as: This is a world



Code:

Dim xlApp As Excel.Application

Dim xlRange As Excel.Range

Dim mtTask As Task

Dim Check As String


Sub ExportMasterData()

'Start Excel and create a new workbook

'Create column titles

'Export data and the project title

'Tidy up



'Start Excel and create a new workbook

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.Workbooks.Add


'Create column titles

Set xlRange = xlApp.Range("A1")

With xlRange

.Formula = "Master Task Report"

.Font.Bold = True

.Font.Size = 12

.Select

End With


xlRange.Range("A2") = "Task ID"

xlRange.Range("B2") = "Name"

xlRange.Range("C2") = "Start"

xlRange.Range("D2") = "Finish"

xlRange.Range("E2") = "Notes"

With xlRange.Range("A2:N2")

.Font.Bold = True

.HorizontalAlignment = xlHAlignCenter

.VerticalAlignment = xlVAlignCenter

.EntireColumn.AutoFit

.Select

End With


'Export data and the project title

Set xlRange = xlRange.Range("A3") 'Set cursor to the right spot in the worksheet

ViewApply Name:="Gantt Chart" 'Get the view that has the Text11 column to filter on

OutlineShowAllTasks 'Any hidden tasks won't be selected, so be sure all tasks are showing

SelectAll

For Each mtTask In ActiveSelection.Tasks 'Pulls data for each task into spreadsheet

With xlRange

.Range("A3") = mtTask.Text1

.Range("B3") = mtTask.Name

.Range("C3") = mtTask.Start

.Range("D3") = mtTask.Finish

.Range("E3") = mtTask.Notes

End With

Set xlRange = xlRange.Offset(1, 0) 'Point to next row

Next mtTask

'Tidy up

ViewApply Name:="Gantt Chart"

With xlRange

.Range("A3").ColumnWidth = 30

.Range("B3").ColumnWidth = 50

.Range("B3").EntireColumn.WrapText = True

.Range("C3").ColumnWidth = 30

.Range("D3").ColumnWidth = 30

.Range("E3").ColumnWidth = 50

.Range("E3").EntireColumn.WrapText = True

End With

Set xlApp = Nothing

End Sub

 

[SkipVought]

Hi,

Have you tried assigning the Wrap property to this range in Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

cleaned up your code...

Option Explicit

Dim xlApp As Excel.Application

Dim mtTask As Task

Dim Check As String


Sub ExportMasterData()
    Dim lRow As Long

    'Start Excel and create a new workbook
    
    'Create column titles
    
    'Export data and the project title
    
    'Tidy up
   
    'Start Excel and create a new workbook
    
    Set xlApp = CreateObject("Excel.Application")
    
    xlApp.Visible = True
    
    xlApp.Workbooks.Add
    
    'Create column titles
    
    With xlApp.ActiveSheet
    
        With .Range("A1")
    
            .Formula = "Master Task Report"
            
            .Font.Bold = True
            
            .Font.Size = 12
        
        End With
        
        .Range("A2") = "Task ID"
        
        .Range("B2") = "Name"
        
        .Range("C2") = "Start"
        
        .Range("D2") = "Finish"
        
        .Range("E2") = "Notes"
        
        With .Range("A2:N2")
        
            .Font.Bold = True
            
            .HorizontalAlignment = xlHAlignCenter
            
            .VerticalAlignment = xlVAlignCenter
            
            .EntireColumn.AutoFit
        
        End With
        
        
        'Export data and the project title
        
        ViewApply Name:="Gantt Chart" 'Get the view that has the Text11 column to filter on
        
        OutlineShowAllTasks 'Any hidden tasks won't be selected, so be sure all tasks are showing
        
        SelectAll
        
        lRow = 3
        
        For Each mtTask In ActiveSelection.Tasks 'Pulls data for each task into spreadsheet
        
            .Cells(lRow, "A").Value = mtTask.Text1
            
            .Cells(lRow, "B").Value = mtTask.Name
            
            .Cells(lRow, "C").Value = mtTask.Start
            
            .Cells(lRow, "D").Value = mtTask.Finish
            
            .Cells(lRow, "E").Value = mtTask.Notes
        
            lRow = lRow + 1
        
        Next mtTask
    
        'Tidy up
        
        ViewApply Name:="Gantt Chart"
        
        With .Range("A3")
            .ColumnWidth = 30
            .Range("A3").Select     'Set cursor to the right spot in the worksheet
        End With
        
        With .Range("B3")
            .ColumnWidth = 50
            .EntireColumn.WrapText = True
        End With
        
        .Range("C3").ColumnWidth = 30
        
        .Range("D3").ColumnWidth = 30
        
        With .Range("E3")
            .ColumnWidth = 50
            .EntireColumn.WrapText = True
        End With
    
    End With
    
    Set xlApp = Nothing

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}