Field Type Memo in Automatic Export

[turnerk200]

Hi there,
I've got an automatic export running off one of my tables into excel. I have recently added a field called "Detail", the field type is memo, when this field exports to excel it cuts half of the contents of the field out. Can anybody suggest the most efficient way to get around this.
Thanks,
KT

 

[thecreator]

An Excel cell (Excel 97 at least) can only hold 255 characters I believe so you will get a truncated result in your sheet ... is your memo bigger than this ??

Hope this helps

 

[turnerk200]

Yes, it is definitely bigger than that.

 

[thecreator]

The only way I can think that you can get around this is to break the memo up into 255 character segments.I do something similar in one of my apps to denormalise a table of notes for export to Excel.

1. Determine the length of the longest memo
2. Divide this by 255, take the integer value of that number and add 1
3. Add this many columns to a table
4. Use MID to loop through your memos and break them into 255 character sets, adding each to one of the fields you have created.

The following sub receives the maximum number of columns (fom step 2) and creates new columns in your table to cater for your memos.

Sub ModifyTables(ItemCount As Integer)
On Error GoTo ModifyTables_Err

Dim dbs As Database, tdf As TableDef, fld As Field, i As Integer, fldName As String

' modifies the base request table to accomodate the maximum number
' of notes on any given assessment in this processing run

Set dbs = OpenDatabase("C:\Temp\dbname.mdb")
Set tdf = dbs.TableDefs!tblMyTableName < insert table name here

For i = 1 To ItemCount

fldName = "Note_" + Trim(Str(i))
Set fld = tdf.CreateField(fldName, dbText, 255)
tdf.Fields.Append fld
tdf.Fields.Refresh

Next i

ModifyTables_Bye:
' release objects
dbs.Close
Set tdf = Nothing
Set dbs = Nothing
Exit Sub

ModifyTables_Err:
MsgBox Err.Description
Resume ModifyTables_Bye

End Sub

If you can get all your notes into a single table (structured like MyKey, Note_1, Note_2 etc) then you can update your main table like this -

Sub RunNotesUpdates()
On Error GoTo RunNotesUpdates_Err

Dim dbs As Database
Dim rst As Recordset
Dim MySQL As String
Dim ColCount As Long
Dim qryTag As String
Dim MyCounter As Long

' find out the max number of columns
MySQL = "SELECT Max(qryCount_Notes_By_Request.Cols_Reqd) AS Col_Count " _
& "FROM qryCount_Notes_By_Request;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(MySQL)

With rst
.MoveFirst
ColCount = !Col_Count
End With

For MyCounter = 1 To ColCount

' Build base SQL clause
MySQL = "UPDATE tblTarget_Requests " _
& "INNER JOIN tblFlat_Notes " _
& "ON tblTarget_Requests.Request_Facts_Key = tblFlat_Notes.Request_Facts_Key " _
& "SET tblTarget_Requests.Note_" + Trim(Str(MyCounter)) + " = [tblFlat_Notes]![Note_" + Trim(Str(MyCounter)) + "];"

' run update
DoCmd.RunSQL (MySQL)

Next MyCounter

RunNotesUpdates_Bye:
Exit Sub

RunNotesUpdates_Err:
MsgBox Err.Description
Resume RunNotesUpdates_Bye

End Sub



Hope this helps

 

[turnerk200]

Hey, thanks for the fabulous detailed response. Will give it a go. For the time being I'm exporting to comma delimited text files.
Thanks again for the great responses.