Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
Suggested Courses

564 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