How to search Notes Field

[rnwolf]

Hi All

I was wondering if there was a way to search for words within task
notes in MSP 2003 + SP1?

All I seem to be able to do is:
Ctrl+F
Type in word that I know is in the notes RTF field
Select Notes field to search
Hit enter
The result set only seems to search the "Summarised" notes column data.
That is the notes column that truncates the text with ....

Any suggestions?

Thanks
Rudi

Ps- I'm thinking of putting meeting notes against the meeting task, but
I need to be able to search the meeting notes text.

 

[John]

Hi All

I was wondering if there was a way to search for words within task
notes in MSP 2003 + SP1?

All I seem to be able to do is:
Ctrl+F
Type in word that I know is in the notes RTF field
Select Notes field to search
Hit enter
The result set only seems to search the "Summarised" notes column data.
That is the notes column that truncates the text with ....

Any suggestions?

Thanks
Rudi

Ps- I'm thinking of putting meeting notes against the meeting task, but
I need to be able to search the meeting notes text.

Rudi,
Gee, I've never tried that type of search but you are correct, the Find
function doesn't really search the Notes field, rather it searches the
column representation of the Notes field. In order to do what you want,
you will need to use VBA. Depending on what output you are seeking, the
VBA code shouldn't be to difficult. It could be as simple as parsing the
actual Notes field for the first occurrence of the match word and then
setting a flag on all tasks with that text. A simple filter can then be
applied to display just those tasks.

Now, if you want some other type of output a different approach would be
necessary, but the bottom line is that VBA will do the trick.

John
Project MVP

 

[rnwolf]

Hi John

Thanks for your suggestion.. You wouldent happen to have a snippet of
VBA arround that I can use to get started on this?

There seem to have been a number of other users in this group, and
related groups, who have struggled to get access to the full text in
the notes field.

Thanks Rudiger

 

[rnwolf]

For my reference and others.

I wrote some VBA code to search for keywords in task notes.
By creating a tool bar button it becomes very useful.
I've also written a subroutine to export all notes in the current view
to excel.

Regards
Rudiger Wolf


Public Const xlHAlignCenter = -4108
Public Const xlHAlignCenterAcrossSelection = 7
Public Const xlHAlignDistributed = -4117
Public Const xlHAlignFill = 5
Public Const xlHAlignGeneral = 1
Public Const xlHAlignJustify = -4130
Public Const xlHAlignLeft = -4131
Public Const xlHAlignRight = -4152
Public Const xlVAlignBottom = -4107
Public Const xlVAlignCenter = -4108
Public Const xlVAlignDistributed = -4117
Public Const xlVAlignJustify = -4130
Public Const xlVAlignTop = -4160


Public Sub SearchNotes()
'------------------------------------------------------------------------
' This function can be used to search for a keyword in the task notes.
' The task list is filtered to display only those that contain the
keyword.
' So one can keep on filtering until you have the desired task and note
' 16 April 2006
'------------------------------------------------------------------------
Dim aTask As Task
Dim MsgBoxReturn As Integer
Dim foundCount As Integer
Dim SearchString As String
Dim longnumber As Long
Dim noteText As String 'The task notes variable

foundCount = 0
SearchString = LCase(InputBox("Enter lowercase keyword to search for"))
SelectAll
For Each aTask In ActiveSelection.Tasks
aTask.Flag15 = False
If Len(aTask.Notes) > 0 Then
noteText = ""
noteText = LCase(aTask.Notes)
If InStr(noteText, SearchString) > 0 Then
'Debug.Print "Found """ & SearchString & """ in task
UniqueID=", aTask.UniqueID
aTask.Flag15 = True
foundCount = foundCount + 1
End If
End If
Next

If foundCount > 0 Then
'Apply a filter for those rows that have Flag15 = True
FilterEdit Name:="FliterSearchNotes", TaskFilter:=True,
Create:=True, OverwriteExisting:=True, FieldName:="Flag15",
Test:="equals", Value:="Yes", ShowInMenu:=False,
ShowSummaryTasks:=False
FilterApply Name:="FliterSearchNotes"
MsgBoxReturn = MsgBox("Keyword found in " & foundCount & " tasks",
vbOKOnly)
Else
MsgBoxReturn = MsgBox("Keyword """ & SearchString & """ not found
any task notes", vbOKOnly)
End If
End Sub


Sub ExportNotesToexcel()
'---------------------------------------------------------------
' This subroutine takes all the task notes in the current view
' opens up excel and exports notes and related info to excel.
' 16 April 2006
'---------------------------------------------------------------
'Open Excel for data dump

Dim s As Object
Dim c As Object
Dim Xl As Object
Dim xlRng As Object
Dim Proj As Project
Dim aTask As Task
Dim aAss As Assignment
Dim AssNames As String

On Error Resume Next
'Check for existing instance of Excel; if not running, start Excel
Set Xl = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0 'clear error function
Set Xl = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation"
_
& Chr(13) & "Install Excel or check network connection",
vbCritical, "Fatal Error"
Exit Sub
End If
End If
'Clear error trap and add a new workbook for each successive run
On Error GoTo 0
Xl.Workbooks.Add
Set xlRng = Xl.ActiveCell

'Keep Excel in the background until spreadsheet is done (speeds
transfer)
Xl.Visible = False
Xl.ScreenUpdating = False
Xl.DisplayAlerts = False

'Write and format title
Set Proj = ActiveProject 'Using Proj is slightly faster
xlRng = "Task Notes Export Report for " & Proj.Name
xlRng.Range("A2") = "As of " & Format(Date, "mmmm d yyyy")
xlRng.Range("A1:A2").Font.Bold = True
xlRng.Font.Size = 12


'Move xlRng below titles
Set xlRng = xlRng.Offset(3, 0)


'Create column titles and format
xlRng.Offset(0, 1) = "UniqueID"
xlRng.Offset(0, 2) = "ID"
xlRng.Offset(0, 3) = "Task Name"
xlRng.Offset(0, 4) = "Start"
xlRng.Offset(0, 5) = "Note"
xlRng.Offset(0, 6) = "Assigned Resources"



'Make excel visible for debuging
'Xl.DisplayAlerts = False
'Xl.ScreenUpdating = True
'Xl.Visible = True

Set xlRng = xlRng.Offset(1, 0) 'Down 1 rows

SelectAll
For Each aTask In ActiveSelection.Tasks
If Len(aTask.Notes) > 0 Then
xlRng.Offset(0, 1) = aTask.UniqueID
xlRng.Offset(0, 2) = aTask.ID
xlRng.Offset(0, 3) = aTask.Name
xlRng.Offset(0, 4) = aTask.Start
xlRng.Offset(0, 4).NumberFormat = "dd mmm yy;@"
xlRng.Offset(0, 5) = Replace(aTask.Notes, vbCr, vbLf) ' Get
rid of the little squares!

AssNames = ""
For Each aAss In aTask.Assignments
AssNames = AssNames & aAss.ResourceName & vbLf
Next aAss
xlRng.Offset(0, 6) = AssNames

With xlRng.EntireRow
.WrapText = True
.RowHeight = 50
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignTop
End With
Set xlRng = xlRng.Offset(1, 0) 'Down 1 rows
End If
Next

'Make excel visible
Xl.ScreenUpdating = True
Xl.DisplayAlerts = True
Xl.Visible = True

End Sub

 

[John]

For my reference and others.

I wrote some VBA code to search for keywords in task notes.
By creating a tool bar button it becomes very useful.
I've also written a subroutine to export all notes in the current view
to excel.

Regards
Rudiger Wolf

Rudiger,
Mind if I ask where you got some of the code, particularly the code for
setting up the instance of Excel? It looks real familiar.

John
Project MVP