Output to Excel from Access - character limit of 255 causing problems!

Posted on 2003-03-14
Medium Priority
Last Modified: 2012-06-27
Hi folks,

I have an Access database which I am running through XP. On the click event a query is run which outputs the selected company's details (approx99lines) to an excel file. Have had no problem with this in the past, until now. One of the fields is a memo field so that we can get more than 255 characters in this field. When this is outputted to Excel the "comments" field, set as a memo field, has been reset to 255 characters in Excel. I have explored why this is and have found out that Excel is set to a field default of 255 characcters. I have therefore changed the default column width to increase the character length. This was then saved as an excel template in the ExcelStart folder so that when a new sheet is selected it is the default setting over 255. Unfortunatly when the outputto function is performed the "commewnts" field is still cut down to 255 characters. The excel file is needed so that the details can be sent by email to other members and loaded into their database into a table. Any ideas on how to get past this? Alternatives?

Look forward to replies, help I'm loosing my hair!
Question by:Warrenp
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 8135642
i tried the docmd.transferspreadsheet but i can't re-create the error. What does your code look like?

/ j

Expert Comment

by:Jonathan Kelly
ID: 8135953
how exactly are u exporting the file ?


Author Comment

ID: 8136112
The code I am using to do the output to function is as follows:

Private Sub Label111_Click()
Dim choice, fname As String
On Error GoTo outputerrorhandler
'fname = "facilitation" & DateForSaving & "." & initials & ".xls"
If QuestionnaireID = 1 Then
DoCmd.OpenQuery "qryIndustrialAnswers"
DoCmd.OutputTo acOutputTable, "TblIndustrialAnswers", acFormatXLS, , False
DoCmd.OpenQuery "qryCommentsTextFacilitation"
DoCmd.OutputTo acOutputTable, "tblCommentsTextFacilitation", acFormatRTF, , False
End If

as you can see i am having to output the same data to a rtf file so that the user can then view the fujll comments field in word then copy and paste this info into the excel file before sending to the appropriate person, who will then load this info into their database, messy I know.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 8136183
i still recommend docmd.transferspreadsheet  

/ j

Expert Comment

by:Jonathan Kelly
ID: 8136225
i would second njelgers comement

if you manually export the file to excel do u get the same problem.

i have tested exporting a memo field with over 1000 chars and it worked fine.

ive never had to use OutputTo

Expert Comment

ID: 8138162
excel has max column width 255, when we export that is the limit. But i tried importig database with memo field it is importing alright, by Data-Import external data-new database query etc. So if we try in access using excel object, it should work.

try something like this in access module.:

sub test()
' Start Excel
       Dim xlapp As Object 'Excel.Application
       Set xlapp = CreateObject("Excel.Application")
       ' Make it visible...
       xlapp.Visible = True
       ' Add a new workbook
       Dim xlbook As Object 'Excel.Workbook
       Set xlbook = xlapp.Workbooks.Open("book1.xls")
  With xlbook.ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=D:\Documents and Settings\manchanda\My Documents\db6.mdb;DefaultDir=D:\Documents and Settings\mancha" _
        ), Array( _
        "nda\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
        ), Destination:=Range("a1"))
        .CommandText = Array( _
        "SELECT Table1.f1, Table1.F2, Table1.nAME, Table1.day, Table1.night, Table1.rem" & Chr(13) & "" & Chr(10) & "FROM `D:\Documents and Settings\manchanda\My Documents\db6`.Table1 Table1" _
        .Name = "Query from MS Access Database_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
       xlbook.Saved = True
       Set xlapp = Nothing
end sub

Author Comment

ID: 8181977
Thanks for all the help guys but this is not working in XP Pro, no problem in XP home or Office 2000, Xp Pro is really giving me a headache with it, any solutions? Also not too sure how to go about the last piece of code somebody sent re: ODBC etc. Although I can get the stransfer spreadsheet command to work in XP home.


Expert Comment

by:Jonathan Kelly
ID: 8182013
maybe there is a patch for XP Pro available ?
LVL 17

Expert Comment

ID: 10337357
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next four days.

EE Cleanup Volunteer

Accepted Solution

PashaMod earned 0 total points
ID: 10364749
PAQed, with points refunded (250)

Community Support Moderator

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question