Export Access to Excel via VBA -- 255 Character Memo Problem

[wrldruler]

I know this is discussed everywhere, but I can't find a problem exactly
like mine. Most are having problems with truncating text at 255, but
mine is different.

When I export a Memo field with > 255 characters to Excel via VBA (see
code below), Excel gives me a #VALUE! error. <255 characters works
fine.

Can someone give me a work-around?

Users enter in updates for their Projects. They hit the Save button,
and I want all of the stuff they just entered to be exported to a very
specific Excel template.

*****************************
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String

Dim appXL As Excel.Application
Dim wbXL As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim strExcelFile As String

'Location of database
strDB = "C:\Profiles\col2pd\Desktop\CSD Work Status and Value
Tracking System ver1.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB &
";"

' Open recordset based on Orders table
Dim str_sql As String
str_sql = "SELECT qry_Master_Report_PCM_PPR.*,
qry_Master_Report_PCM_PPR.tbl_Reports_PCM_PPR.PCM_PPR_ID FROM
qry_Master_Report_PCM_PPR WHERE
(((qry_Master_Report_PCM_PPR.tbl_Reports_PCM_PPR.PCM_PPR_ID)=33));"

rst.Open str_sql, cnt

'Sets the name of template
strExcelFile = "G:\WIL\CustomerCare\CSS\E&T\Chris\PMIS Project\03 -
Design\Design Proto DB\CSD Request and Tracking Databases\CSD Status
Tracking Database\Templates\PCM_PPR_Template.xls"

'Sets properties on new Excel window
Set appXL = New Excel.Application
With appXL
.Visible = True
.WindowState = xlMaximized 'xlNormal
.Workbooks.Add strExcelFile
Set wbXL = appXL.ActiveWorkbook
Set oSheet = wbXL.ActiveSheet
End With

'Puts the data where it belongs
oSheet.Range("F2").Value = rst.Fields("Project_Name")
oSheet.Range("E3").Value = rst.Fields("PM_Full_Name")
oSheet.Range("E4").Value =
rst.Fields("tbl_Reports_PCM_PPR.PCM_PPR_ID")
oSheet.Range("N3").Value = rst.Fields("Project_Core_PO_Name")
oSheet.Range("N4").Value = rst.Fields("First_Project_ID")
oSheet.Range("C6").Value = rst.Fields("Project_RAG_Overall_Letter")
oSheet.Range("D6").Value = rst.Fields("Project_RAG_Scope_Letter")
oSheet.Range("E6").Value =
rst.Fields("Project_RAG_Resources_Letter")
oSheet.Range("F6").Value = rst.Fields("Project_RAG_Sched_Letter")
oSheet.Range("G6").Value = rst.Fields("Project_RAG_NPV_Letter")
oSheet.Range("H6").Value =
rst.Fields("Project_RAG_Ben_Date_Letter")
oSheet.Range("I6").Value = rst.Fields("Financial_NPV_5_Year")
'All of the above works perfect, but I am having problems with the
field below which is > than 255 characters
oSheet.Range("K6").Value = rst.Fields("PCM_PPR_Key_Message")

******************
Thanks

 

[Ken Snell \(MVP\)]

What string text is in that memo field? My initial guess is that EXCEL is
interpreting the string as some type of formula, hence the #VALUE! error.
Post some examples of the data that are in that field.