export to excel without cutting off data

[Jen]

I have an application that should be exporting long strings from an Access
query to an excel spreadsheet. Each week the spreadsheet should be
overwritten. The problem that I'm finding is that the long strings are cut
off (I'm assuming at the 255 char). I have tried the following (as well as
output to) and haven't had any luck. I'm not really proficient with code so I
would need a lot of help if the export has to be done row by row. Any ideas?

Option Compare Database
Public Function CommentConcatenate() As Long
On Error GoTo CommentConcatenate_Err

Dim strSQL As String
Dim strFileFolder As String
Dim strFile As String
Dim strFileSpec As String

strFileSpec = "C:\Documents and Settings\jstandley-magdech\My
Documents\Comments.xls"

'Kill the file
If Len(Dir(strFileSpec)) > 0 Then 'File exists
Kill strFileSpec
End If

strFile = "Comments"

'Template and file save path
strFileFolder = "C:\Documents and Settings\jstandley-magdech\My
Documents\"

If strFile = "" Or IsNull(strFile) Then
GoTo CommentConcatenate_Exit
End If

If strFileFolder = "NA" Then
Err.Raise vbObjectError, , "Unabled to find the save path.",
vbExclamation, CMSG_TITLE
Else
strFile = strFileFolder & strFile & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"qryCommentConcatenate", _
strFile, True

CommentConcatenate_Exit:
Exit Function

CommentConcatenate_Err:
MsgBox Err.Description, vbExclamation, CMSG_TITLE
Resume CommentConcatenate_Exit

End Function

 

[Jeanette Cunningham]

Jen,
you can export memo fields without the strings being cut off.
You can concatenate fields together in a query.
When you concatenate fields together in a query you end up with a calculated
field of text type, not memo type - this is where the problem occurs.
So exporting a concatenated field from a query results in strings being cut
off at 255 characters.

To work around this:
Create a table with the fields already set up as you need them.
This table will have a memo field that receives the data that will be
concatenated together as well as any other fields you need to export.
Add the data to be concatenated like this.

-------------------------------
strSQL = "UPDATE tblTemp " _
& "SET tblTemp.TheMemoFieldName = tblOriginal.FieldA &
tblOriginal.FieldB &
tblOriginal.FieldC
CurrentDb.Execute strSQL, dbFailOnError
-----------------------------

Replace the table and field names with your own names.
Export this table instead of the previous query.

I usually use a table in a temporary database to do this.
When finished I just delete the temporary database.
This stops your database from getting bloated.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Jen]

Jeanette,

Thank you. The concatenate is not being done between different fields in one
record but for different records that are related - we have a comments table
where each line of a comment is its own record (not my idea & not something I
can change). The comments need to be combined & exported to excel. So the
question is would I then just replace "tblOriginal.FieldA &
tblOriginal.FieldB & tblOriginal.FieldC" with the query's concatenated field?

Thanks,

 

[Jeanette Cunningham]

Jen,
the same overall idea applies.
I also do this, concatenate the child records together into one record for
the parent record.
The idea is to concatenate each comment together into a single field for the
same parent record.
Do you need to know how to do this?
Here is a link that shows how to do this.
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

However, you need to concatenate your comments into a memo field not a text
field.
If you are still stuck, please post details of the tables and queries
involved - names, relevant field names, primary keys and relationship.


Jeanette Cunningham -- Melbourne Victoria Australia