Excel automation - need more than 255 characters in a cell

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

I have an Access 2002 query where I'm calling a function named fConcatChild
to return a concatenated text string which is somtimes greater than 255
characters in length. The query results are then exported to an Excel
spreadsheet using automation. The maximum cell width for displaying text in
Excel appears to be 255 characters, although I've received some promising
tips from the Excel forum concerning inserting manual line breaks in the
text in order to display more than 255 characters, and that 32,000
characters can actually be stored in a cell. I tried the code below, but so
far it appears I'm still getting only 255 characters displayed.

Any thoughts would be appreciated.

Bill
_________________________________________________________

I'm having trouble inserting a manual line break in my text, so I must have
a syntax problem. I'm doing this in a function named fConcatChild, and
returning the results to a query which is then exported to Excel using Excel
automation from within Access. Here's the code in my function:

' insert a hard return for display in Excel if the string is longer than
255
If Len(fConcatChild) > 255 Then
fConcatChild = Left(fConcatChild, 255) & vbCrLf &
Right(fConcatChild, Len(fConcatChild) - 255)

End If

I'm still getting only about 255 characters in the cell in Excel to which
this variable is exported. Am I using the wrong Visual Basic constant
vbCrLf?

Bill
 
It appears my problem actually lies with the Access query. The string being
returned by function fConcatChild is sometimes longer than 255, and the
query is only retaining the first 255 characters in the query column. Is
there a way to increase this to say 1,000? This truncated 255 characters is
being exported to Excel. In further reading I found that up to 1,000
characters can be displayed in an Excel cell, so long as the cell is
formatted as text.

Bill


Bill Murphy said:
I have an Access 2002 query where I'm calling a function named fConcatChild
to return a concatenated text string which is somtimes greater than 255
characters in length. The query results are then exported to an Excel
spreadsheet using automation. The maximum cell width for displaying text in
Excel appears to be 255 characters, although I've received some promising
tips from the Excel forum concerning inserting manual line breaks in the
text in order to display more than 255 characters, and that 32,000
characters can actually be stored in a cell. I tried the code below, but so
far it appears I'm still getting only 255 characters displayed.

Any thoughts would be appreciated.

Bill
_________________________________________________________

I'm having trouble inserting a manual line break in my text, so I must have
a syntax problem. I'm doing this in a function named fConcatChild, and
returning the results to a query which is then exported to Excel using Excel
automation from within Access. Here's the code in my function:

' insert a hard return for display in Excel if the string is longer than
255
If Len(fConcatChild) > 255 Then
fConcatChild = Left(fConcatChild, 255) & vbCrLf &
Right(fConcatChild, Len(fConcatChild) - 255)

End If

I'm still getting only about 255 characters in the cell in Excel to which
this variable is exported. Am I using the wrong Visual Basic constant
vbCrLf?

Bill
Click to expand...
 
Bill said:
It appears my problem actually lies with the Access query. The string being
returned by function fConcatChild is sometimes longer than 255, and the
query is only retaining the first 255 characters in the query column. Is
there a way to increase this to say 1,000? This truncated 255 characters is
being exported to Excel. In further reading I found that up to 1,000
characters can be displayed in an Excel cell, so long as the cell is
formatted as text.

Bill
Click to expand...

I don't know if this will help you with Excel, but I ran the following
Query:

SELECT Left$(fConcat(),283) AS String283
FROM Table1;

and in Query Datasheet View (Access 2000), the string that appeared
ended with the following characters:

" ...'..260 ...'..270 ...'..280 .."

Function fConcat() is defined as follows:

Public Function fConcat()
Const strStuffing As String = " ...'.."
Dim str300 As String
Dim intN As Integer

For intN = 1 To 30
str300 = str300 _
& strStuffing _
& Format$(intN, "00") & "0"
Next intN
fConcat = str300

End Function ' fConcat()
 
Hi Bill,

This is an irritating standard "feature": when you export a query,
strings returned by calculated fields are treated as text fields and
therefore truncated at 255 characters.

One way round it is to turn the query into an append query, append the
records to a table in which the fields in question are defined as memo
fields and then export the table.

Another, if you're using automation anyway, is to open a recordset on
the query and put the values from the recordset into the worksheet,
either by using Excel's Range.CopyFromRecordset method or by iterating
through the recordset's records and fields and setting the Value
properties of the corresponding cells in the worksheet.

It appears my problem actually lies with the Access query. The string being
returned by function fConcatChild is sometimes longer than 255, and the
query is only retaining the first 255 characters in the query column. Is
there a way to increase this to say 1,000? This truncated 255 characters is
being exported to Excel. In further reading I found that up to 1,000
characters can be displayed in an Excel cell, so long as the cell is
formatted as text.

Bill
Click to expand...
 
Back
Top