Truncation problem

[Bakema]

Hi there,

How do I avoid a truncation error when exporting an
Access memo field to Excel with over 255 characters. It
creates a paste error table everytime I export this table
to excel.

Thanks for your help

Bakema

 

[Joe Fallon]

Known issue for Office97.

Fixed in 2000 and newer.

 

[Bakema]

Thanks Joe, but I am running office 2K. Any other ideas?

Bakema

 

[Ken Snell]

You may need to use VBA code and write directly into the EXCEL worksheet's
cells via Automation. Exporting value to and importing values from EXCEL
when those values are longer than 255 characters has quirks...sometimes it
works, sometimes it doesn't.

To be safe, I use Automation. Post back if you need more info on how to do
this.

 

[Joe Fallon]

Try using TransferSpreadsheet command in code.
Then you can specify the version of Excel.

The menu commands use an old version of Excel by default.
This old version may have the problem.

 

[Marty]

I am having the same issue with Acces 2000 & Excel 2000.
Could you explain the use of VBA code?

 

[Ken Snell]

VBA = Visual Basic for Applications.

It's the programming language that is used in ACCESS modules.

What you would need is a subroutine that you call in order to do the export.
Create a module (Database window | Modules | New) and name it basSubs.

Paste this generic code (not tested) into the VBE window (change generic
names to your real names) and then modify it as you need to for your setup:


Public Sub ExportingToEXCEL()
Dim xls As Object, xlWB As Object, xlWS As Object
Dim xlCell As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intF As Integer

Set xls = CreateObject("Excel.Application")
Set xlWB = xls.Workbooks.Open InputBox("Enter the " & _
"path and filename of the EXCEL file:")
Set xlWS = xlWB.Worksheets(1)
Set xlCell = xlWS.Range("A1")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(InputBox("Enter name of " & _
"table or query to be exported:"), dbOpenDynaset, _
dbReadOnly)
If rst.BOF = False And rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
For intF = 0 To rst.Fields.Count - 1
xlCell.Offset(0, intF).Value = rst.Fields(intF).Value
Next intF
rst.MoveNext
xlCell = xlCell.Offset(1,0)
Loop
End If

Set xlCell = Nothing
Set xlWS = Nothing
xlWB.Close True
Set xlWB = Nothing
xls.Quit
Set xls = Nothing

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub