Truncated Notes field when export to Excel

[Uppie]

I use the Notes indicator on a task to provide additional details to a particular task. When in export this field to EXCEL it is truncated in the EXCEL file? Is this a function of Project or a function of EXCEL. I'm using EXCEL 2002.

 

[Mike Glen]

Hi Uppie,

Welcome to this Microsoft Project newsgroup

This is an old chestnut :-( What little you can do can be seen in FAQ Item:
21. Truncated Notes.

FAQs, companion products and other useful Project information can be seen at
this web address: http://www.mvps.org/project/

Hope this helps - please let us know how you get on

Mike Glen
Project MVP

 

[John]

Uppie,
If you are using Project 2000 or later, all the text in the Notes field
will export to Excel. However, Excel has a limit of 1024 characaters in
any given cell after which text wrap will not work.

How are you exporting the data to Excel? At what point is the
information truncated? There are workarounds.

John

 

[Chuck]

Hi everyone- I'm having the same truncation problem, however it's not just with Excel. I'm using Project 2002, and I have even tried to copy a large not from one task to another in the same project - the note truncates. It would be very useful if I could export my notes and use the Excel data functions to parse my notes into separate fields.

 

[John]

Michael/Chuck,
Whenever I export data from Project to Excel I always use a VBA macro
and that works fine except for the 1024 word wrap limit in Excel as
noted before. However, I did a test case using the export map feature
and sure enough, the data is truncated at 255 characters. Apparently
with an export map the Notes field is exported and the Notes field
truncates at 255. However if the content of the Notes box in the task
information window is transferred, the full text transfers. The
difference is that the text stored in Project's task database is the
full text as entered in the task information Notes text box while the
Notes field (i.e. task view column) is a truncation of the full text in
the database. Ok, that's the theory of operation, so what do you do
about it?

If you do not want to use a VBA macro, spread the extended Notes field
text over one or more spare text fields (e.g. Text1, etc.) so each is
255 characters or less. Then set up the export map to export each
"segment" of the text and re-combine the text once it is in Excel.

If you want to try a macro but have no VBA experience, write me direct
and I'll get you started.

Chuck: If you copy the contents of the Notes text box in the Task
Information window from one task to another, you will get the full text.
But again, that same information viewed in the Notes field will be
truncated.

John

 

[Patrick]

John

I have encountered the same problem but I want to save my notes to an Access database format so that I can create truly custom reports. I have searched the web for ome sample VB code but have been unsuccesful. I have very little prgramming knowledge but in the past I have been able to adapt code to suit my needs. I adapted some VB code in MS Access to concatenate data from multiple records

I would like to have VB code that extracts the Text_REF_UID field and the TEXT_VALUE field to a MS Access table. Does the use of EOL (End of Line - Return/Enter) pose any issues

Thanks

----- John wrote: ----


Michael/Chuck
Whenever I export data from Project to Excel I always use a VBA macro
and that works fine except for the 1024 word wrap limit in Excel as
noted before. However, I did a test case using the export map feature
and sure enough, the data is truncated at 255 characters. Apparently
with an export map the Notes field is exported and the Notes field
truncates at 255. However if the content of the Notes box in the task
information window is transferred, the full text transfers. The
difference is that the text stored in Project's task database is the
full text as entered in the task information Notes text box while the
Notes field (i.e. task view column) is a truncation of the full text in
the database. Ok, that's the theory of operation, so what do you do
about it

If you do not want to use a VBA macro, spread the extended Notes field
text over one or more spare text fields (e.g. Text1, etc.) so each is
255 characters or less. Then set up the export map to export each
"segment" of the text and re-combine the text once it is in Excel

If you want to try a macro but have no VBA experience, write me direct
and I'll get you started

Chuck: If you copy the contents of the Notes text box in the Task
Information window from one task to another, you will get the full text.
But again, that same information viewed in the Notes field will be
truncated

Joh

 

[William Raymond]

Hi Patrick,

If I remember correctly, I downloaded this code from the MS Project website
a l-o-n-g time ago. Anyway, you can use this to connect to the database and
extract the notes field. You will need to use VBA in MS Project to paste
the code in and run it. -Bill

Option Compare Database
Option Explicit

Sub getrtf()
Dim db As Database
Dim rs As Recordset
Dim bytBuffer() As Byte
Dim strData As String, strExtracted As String
Dim intSize As Integer, intOffset As Integer

'This macro will look for Task Notes and extract the rtf. This rtf can then
be written to a file
'that Word will understand or displayed in a richedit control.

'Open the Task_Information table to look for Task Notes
Set rs = CurrentDb.OpenRecordset("Task_Information", dbOpenTable)

'Enumerate across the recordset looking for notes
With rs
Do While Not .EOF
If !Reserved_hasnotes <> 0 Then

' Extract string
strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode) ' Put binary
column data into text string
intSize = GetIntegerFromPosition(strData, 1) ' Size starts at position 1
intOffset = GetIntegerFromPosition(strData, 5) ' Offset starts at 5
strExtracted = Mid(strData, intOffset + 1, intSize)
Debug.Print strExtracted

End If
..MoveNext
Loop
..Close
End With

End Sub

Function GetIntegerFromPosition(s As String, p As Long) As Integer
Dim i As Long, intResult As Integer
For i = p + 3 To p Step -1
intResult = intResult * 255 ' Shift one byte
intResult = intResult + Asc(Mid(s, i, 1))
Next i
GetIntegerFromPosition = intResult
End Function



--
Bill Raymond
projectnation at hotmail dot com

John,

I have encountered the same problem but I want to save my notes to an

Access database format so that I can create truly custom reports. I have
searched the web for ome sample VB code but have been unsuccesful. I have
very little prgramming knowledge but in the past I have been able to adapt
code to suit my needs. I adapted some VB code in MS Access to concatenate
data from multiple records.

I would like to have VB code that extracts the Text_REF_UID field and the

TEXT_VALUE field to a MS Access table. Does the use of EOL (End of Line -
Return/Enter) pose any issues?

 

[Steve]

I'm using MS Project 2002 and Excel 2002, and the same thing is occuring. Doesn't MS have a fix for this by now??? This is frustrating. Seems to me this should be a basic function and one shouldn't have to go and write VBA code to accomplish a simple task. I can live with the 1024 char. limit, but not the 255.

----- Uppie wrote: -----

I use the Notes indicator on a task to provide additional details to a particular task. When in export this field to EXCEL it is truncated in the EXCEL file? Is this a function of Project or a function of EXCEL. I'm using EXCEL 2002.

 

[bearbie]

Has anyone found a quick way to export full task notes to excel? I'm
using Project 2003 and Excel 2003, and still can't do it directly.

I tried to export to Access DB first, but there I was stuck because the
Task_RTF_NOTES field is "Long Binary Data", and I didn't know how to
extract text from that.

So I tried exporting the project to an XML file, then import the XML
data from MS Excel. Thanks god, that worked out well. So I only needed
to delete the unwanted columns (quite a few) to use the Name and Notes
column.

 

[John]

Has anyone found a quick way to export full task notes to excel? I'm
using Project 2003 and Excel 2003, and still can't do it directly.

I tried to export to Access DB first, but there I was stuck because the
Task_RTF_NOTES field is "Long Binary Data", and I didn't know how to
extract text from that.

So I tried exporting the project to an XML file, then import the XML
data from MS Excel. Thanks god, that worked out well. So I only needed
to delete the unwanted columns (quite a few) to use the Name and Notes
column.

bearbie,
I have a VBA macro that exports the Notes text to Excel. If you are
interested, contact me direct.

John
Project MVP

 

[Stephen]

I would appreciate that macro. please let me know how to contact you or
please post your code.

Thanks

Stephen

 

[bharat]

Can you email me the VBA macro please..

bearbie,
I have a VBA macro that exports the Notes text to Excel. If you are
interested, contact me direct.

John
Project MVP

 

[John]

Can you email me the VBA macro please..

bharat,
Contact me direct and we'll go from there.

John

 

[wabasso]

To avoid using macros, save the file as XML in MS Project. Then, open this file in Excel.

There will be a lot of fields to sort through, but I found the Notes field under "ns1:Notes" and the project name under "ns1:Name4"