Memo field to excel

[netcashin]

In a VB application I am creating an Excel spreadsheet. In code I select data from an access database and then write to cells in the spreadsheet from VB. Memo fields that are longer than 255 show up as #Value!. Any ideas to keep this from happenning?

 

[JoaoTL]

Hi, you are passing an SQL stringlonger than 255 characters

See this example from the Nortwind Microsoft MDB, to pass plus 255 characters

Sub CreatePivotTableFromMDB()
' This procedure creates a PivotTable report from an external
' database, the Northwind sample database.

Dim cnnNwind As ADODB.Connection
Dim cmdQuery As ADODB.Command
Dim catDb As ADOX.Catalog
Dim wksPivot As Worksheet
Dim pvtTable As PivotTable
Dim lngWksCount As Long
Dim varSource As Variant
Dim strConnect As String

' Add new worksheet to end of workbook.
Set wksPivot = AddWorksheetToEnd(ThisWorkbook, REGIONAL_SALES)

' Create new query in Northwind and append to Views collection.
' This is one way to handle the problem of passing an SQL string
' longer than 255 characters.
' Open new ADO connection.
Set cnnNwind = New ADODB.Connection
cnnNwind.Open ADO_CONNECT_STRING
Set catDb = New ADOX.Catalog
' Open catalog on data source.
Set catDb.ActiveConnection = cnnNwind
' Create new command.
Set cmdQuery = New ADODB.Command
' Specify SQL string as command text.
cmdQuery.CommandText = REGIONAL_SALES_SQL

' Before appending new view, delete query with the
' same name, if it exists.
On Error Resume Next
catDb.Views.Delete QUERY_NAME
On Error GoTo 0

' Append new view.
catDb.Views.Append QUERY_NAME, cmdQuery

' Create array containing connection string and SQL string.
varSource = Array(ODBC_CONNECT_STRING, "SELECT * FROM " & QUERY_NAME)

' Create PivotTable report. Start at cell B8 so there is enough
' room for page fields.
Set pvtTable = wksPivot.PivotTableWizard(xlExternal, varSource, _
wksPivot.Range("B8&quot)

' Specify initial fields to appear in PivotTable report.
With pvtTable
' Specify row field.
.PivotFields("Country&quot.Orientation = xlRowField
' Specify column fields. Specify their relative positions
' in the table -- year should come before quarter.
With .PivotFields("Year&quot
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Quarter&quot
.Orientation = xlColumnField
.Position = 2
End With
' Specify page fields.
.PivotFields("CategoryName&quot.Orientation = xlPageField
.PivotFields("ProductName&quot.Orientation = xlPageField
.PivotFields("LastName&quot.Orientation = xlPageField
' Specify data field.
.PivotFields("ProductPrice&quot.Orientation = xlDataField
' Format data region as currency.
.DataBodyRange.NumberFormatLocal = "$#,##0.00"

' Turn off subtotals for Year field.
' You need to pass in an array of Boolean values as shown
' below so that any subtotal that is currently on will be
' turned off.
.PivotFields("Year&quot.Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
End With
End Sub
Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access