SQL 255 char limit to Excel

[makla]

Hi there,

I am trying to fill an Excel cell with a variable from an access database. The database definition of this field is memo.

After 255 characters the variable gets truncated.

Does anybody know how to get all data and not only 255 chars?

Many thanx!!!!

The source code I use is:

Dim PROBLEEMOMSCHRIJVING As Variant
PROBLEEMOMSCHRIJVING = ALPR00PF("POAANL") 'i.v.m. >255 chars

BEREIK = "F" & RIJNUMMER
sExcel.Worksheets(WERKBLAD).Range(BEREIK).Value = PROBLEEMOMSCHRIJVING

[Lord_Rat]

MSDN Library:

ACC2000: Memo Field Truncated When Report Is Output to Excel

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access 2000

--------------------------------------------------------------------------------

Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
When you output a report to Microsoft Excel, any Memo field is truncated to 255 characters. You may also see this behavior when you click the Analyze It With MS Excel command while you are previewing a report.



CAUSE
In Excel, the maximum length of text-cell contents is 32,000 characters. However, Access outputs a report to Excel 5.0/95 format, in which the maximum length of text-cell contents is 255.



RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:



http://mcspreferral.microsoft.com/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:


http://www.microsoft.com/support/sup...w/overview.asp
Method 1
Export the underlying table or query to Excel 2000 file format. To do so, follow these steps:
In the Database window, select the table or query on which the report is based.


On the File menu, click Export.


In the Export Table '<Table Name>' dialog box, select Microsoft Excel 97-2000 (*.xls) from the Save as Type box.


Click Save to export the table.


Method 2
Create text boxes on the report to split the Memo field into 250-character strings. After you have output the report, reassemble those strings.

For example, assume you have a Memo field named Notes, and in some records, the contents of that field is 700 characters long. You can create three text boxes in the report with each of the following three expressions as the control source for one of the text boxes:
=Mid([Notes],1,250)

=Mid([Notes],251,250)

=Mid([Notes],501,250)
After you have output the report to a spreadsheet, you can reassemble the segments of the Memo field. For example, the segments of the Memo field of the first record appear in cells B2, C2, and D2 respectively. You can reassemble the Memo field by typing the following formula in another cell, E2:
=CONCATENATE(B2,C2,D2) )
You can then copy this formula to the rest of the cells in column E to reassemble the Notes field for all of the records.

Note that the CONCATENATE function returns the error #VALUE when you try to use the formula
=CONCATENATE(B22) )
The address of every cell whose contents you want to include in the concatenated result must be listed separately.

If the length of the data in the Memo field makes it necessary for you to create more than a few text boxes, you can use a Visual Basic for Applications procedure to automate the creation of those text boxes. The following example demonstrates how to use a procedure to create the text boxes, and then output the report and reassemble the contents of the text box.
Follow steps 1 through 7 in the "Steps to Reproduce Behavior" section later in this article.


Create a new report in Design view based on the tblMemoOutput table.


Drag the ID field to the detail section of the report.


Save the report as rptMemoOutput.


Open a new module and type the following procedure:



Function MemoSplitter(strReportName As String, _
strFieldName As String, lngMemoLength As Long)

Dim NewControl As Control
Dim intLoopCount As Integer
For intLoopCount = 0 To lngMemoLength / 250

Set NewControl = CreateReportControl(strReportName, _
acTextBox, acDetail)
NewControl.Name = intLoopCount & "MemoText"
NewControl.ControlSource = "=Mid([" & _
strFieldName & "]," & 250 * intLoopCount + 1 _
& ",250)"
Next intLoopCount

End Function
Compile and save the module as mdlSplitFunction.


Type the following in the Immediate window, and then press ENTER:



?MemoSplitter("rptMemoOutput","Notes",5200)
View the report in Print Preview. On the Tools menu, point to Office Links, and then click Analyze It With MS Excel.


When the data appears in a spreadsheet file in Microsoft Excel, click cell A1; press CTRL+SHIFT+END to select all of the data.


On the Format menu, point to Row, and then click Autofit.


Select cell A2. On the Insert menu, click Rows.


Type the following formula in cell A2:


=VALUE(LEFT(A1,FIND("M",A1,1)-1))
Copy the formula to cells B2 - U2.


Select cells A2 - U2.


On the Edit menu, click Copy.


On the Edit menu, click Paste Special.


Under Paste in the Paste Special dialog box, click Values, and then click OK. This converts the formulas in the second row to values, which you can use to sort the segments of the Notes field.


Click cell A2 and press CTRL+SHIFT+END to select all but the first row of data.


On the Data menu, click Sort.


In the Sort dialog box, click Options.


Under Orientation in the Sort Options dialog box, click Sort Left To Right. Then click OK.


In the Sort dialog box, click Row2 in the Sort By list, and then click OK to sort the segments of the Notes field.


In cell W3, type the following formula:


=CONCATENATE(A3,B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,N3,O3,P3,Q3,R3,S3,T3,U3)
Copy the formula to cells W3 and W4 to reassemble the Notes field for each record.





STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.



MORE INFORMATION

Steps to Reproduce Behavior
Start Microsoft Access and open any database.


Create the following table named tblMemoOutput:



Table: tblMemoOutput
--------------------
Field Name: ID
Data Type: Text
Field Name: Notes
Data Type: Memo
Save the table as tblMemoOutput. When prompted to create a primary key, click No.


Open the table in Datasheet view and enter the following records:



ID Notes
-- -----
a a
b b
c c
Open a new module and type the following procedure:



Function FillMemo(strTableName As String, _
strFieldName As String)

Dim db As Database
Dim rs As Recordset
Dim intLoopCount As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName)

Do Until rs.EOF
rs.Edit

For intLoopCount = 1 To 26
rs(strFieldName) = rs(strFieldName) _
& String(200, Chr(intLoopCount + 64))
Next intLoopCount

rs.Update
rs.MoveNext
Loop

db.Close

End Function

Compile and save the module as mdlDataFunction.


Enter the following in the Immediate window, and then press ENTER:



?FillMemo("tblMemoOutput","Notes")
Use the AutoReport: Columnar Wizard to create a report based on tblMemoOutput. View the report in Print Preview.


On the Tools menu, point to Office Links, and then click Analyze It With MS Excel.


When the data appears in a spreadsheet file in Microsoft Excel, type the following formula into cell C2:
=LEN(B2)
Note that the data in the Notes field has been truncated to 255 characters.





REFERENCES
For more information about loading the output of a report into Microsoft Excel, click Microsoft Access Help on the Help menu, type outputting data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.


Additional query words: blob

Keywords : kbdta OtpProb OtpExl RptProb
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug
Technology :


Last Reviewed: April 7, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.

[Ecniv]

But they are not exporting to xls.... they are directly writing to excel.

Hmmm, unless its Excel 97.... :/


Perhaps its the value property. When I've copied code from Excel 2k its always wanting to put the values in as Functions (for some reason).

Easiest way to test - open Excel, goto Tools>Macro>Record New
The type into one of the cells then stop recording. Goto Tools>Macro>VB Editor and view the code (should be in a module) and see how it does it. If this doesn't solve it then you'll have to do as Lord rat stated from MS - and split the Memo field..

Good Luck

Vince