Hi,
Happy new year to all(:
I have a Microsoft Access Database which stores comments. Comments are 'Memo' typed. Thus they can be very very long. However i need to export the comments into excel monthly but the 255 character limit refrains me from exporting the entire comments stores in the Access database.
I have tried to find solutions for this as it is a common problem, but to no avail.
Many thanks in advance for the help and to a great year ahead!
Nicole
I was trawling through the many items on the Allen Browne Access web site tonight and found a reference to a Microsoft Knowledgebase article on the 255 character issue when exporting reports (not queries): Memo Field Truncated When Report Is Output to Excel. According to the article, truncation to 255 characters results from Access exporting data in Excel 5.0/95 format, a much earlier version of Excel where the 255 character limit for text in a cell did indeed apply.
It may not matter now that another solution has been found, but it is at least another view on what may have been wrong originally.
(By the way, the second "solution" for this problem proposed in the MS article is just plain silly IMHO - split the memo field into 250 character components and then concatenate them back together again in Excel. How slick!)
-Stewart
7 23393
When exporting a table without using a query, the 255 limit won't be used by Access. Just when you "manipulate" the field in a query it will be truncated.
There's however an additional limit, that of the excel cell :-(
The only 100% solution I know is to export the table to a Word file (e.g. .rtf) to get all data visible without truncation.
Nic;o)
Obviously you can't, with Excel having a column width limit of 255 characters! I think more importantly here is the question of why you feel the need to export it to Excel in the first place! If you're doing it because you need to provide the data to people who don't have Access on their PCs there are other ways to do this.
Welcome to Bytes!
Linq ;0)>
From Microsoft: "Only 1,024 characters are displayed in a cell in Excel" http://support.microsoft.com/kb/211580
But it can contain a max of 32,767 characters....
The display problem has been solved in Excel 2007.
Nic;o)
I stand corrected, Nic;o) ! The 255 character maximum was for Column Width, which is actually how how wide a column can be, but you can have multiple rows in a column and hence display more characters in a cell.
Doesn't appear to matter, as the OP appears to have lost interest!
Linq ;0)> NeoPa 32,556
Expert Mod 16PB
I expect you're hitting an unnecessary limit here Nicole. As the boys have said, there are many more characters that can be passed without needing to resort to other methods.
Perhaps if your requirement is an unlimited length field, then a different approach is called for. As with any such process, you need to consider the parameters of your project before deciding what is appropriate for the solution. Although Excel can handle many more characters than 255, there are nevertheless length limits which will cause you issues. To find the most appropriate solution we'd need a better understanding of your requirements.
I was trawling through the many items on the Allen Browne Access web site tonight and found a reference to a Microsoft Knowledgebase article on the 255 character issue when exporting reports (not queries): Memo Field Truncated When Report Is Output to Excel. According to the article, truncation to 255 characters results from Access exporting data in Excel 5.0/95 format, a much earlier version of Excel where the 255 character limit for text in a cell did indeed apply.
It may not matter now that another solution has been found, but it is at least another view on what may have been wrong originally.
(By the way, the second "solution" for this problem proposed in the MS article is just plain silly IMHO - split the memo field into 250 character components and then concatenate them back together again in Excel. How slick!)
-Stewart
I came across the same error recently & thought it would have been solved now in Access 2010, but sadly not. The problem exists if using the DoCmd.OutputTo or DoCmd.TransferSpreadsheet methods and you have any table columns with more than 255 characters. My workaround is to write the data to an Excel object instead, rather than splitting fields into 255 chunks which is crazy.
If your data is in a table or simple query, call the SendToExcel function as follows: - SendToExcel "SELECT * FROM MyTable"
- Public Function SendToExcel(sSQL As String)
-
-
' Creates a new Excel Workbook/Worksheet & writes the specified data into it.
-
' Note you can't export a parameter query, must create a temp table first in this case.
-
-
' Use Late Binding to prevent need to include Excel Object Library References
-
' thus making it independent of MS Office version
-
-
Dim xlApp As Object
-
Dim xlBook As Object
-
Dim xlSheet As Object
-
-
Dim lvlColumn As Integer
-
-
Dim cnn As New ADODB.Connection
-
Dim rst As New ADODB.Recordset
-
-
Set cnn = Application.CurrentProject.Connection
-
rst.Open sSQL, cnn
-
-
Set xlApp = CreateObject("Excel.Application")
-
Set xlBook = xlApp.Workbooks.Add
-
Set xlSheet = xlBook.Worksheets(1)
-
xlSheet.Activate
-
xlSheet.Application.Visible = True
-
-
'Loop through the fileds collection and make each field name a column heading in Excel
-
For lvlColumn = 0 To rst.Fields.Count - 1
-
xlSheet.Cells(1, lvlColumn + 1).Value = rst.Fields(lvlColumn).Name
-
Next
-
-
'Change the font to bold for header row
-
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, rst.Fields.Count)).Font.Bold = True
-
-
'Send data from Recordset out to Excel
-
xlSheet.Range("A2").CopyFromRecordset rst
-
-
'clear up objects
-
rst.Close
-
Set rst = Nothing
-
Set xlSheet = Nothing
-
Set xlBook = Nothing
-
Set xlApp = Nothing
-
-
End Function
If you have a parameterised query however you will need to write it first into a temporary table, then remove this after the export. This may not be the neatest solution but it works.
Call the SendToExcel function instead as follows, where objName is the name of your parameterised query. - Public Function ExportToExcel(objName As String)
-
-
' Export a table/query to Excel by first saving to temp table then writing contents to Excel.
-
' This is to get around the issue of 255 character limit in any field when using the built in
-
' Access functions such as OutputTo or TransferSpreadsheet.
-
-
'hide warnings
-
DoCmd.SetWarnings False
-
-
'Create a temp data table
-
DoCmd.RunSQL "SELECT * INTO ExportData FROM [" & objName & "]"
-
-
'Write the contents of the table out to Excel
-
SendToExcel "SELECT * FROM ExportData"
-
-
'Delete the temp Export table
-
DoCmd.DeleteObject acTable, "ExportData"
-
-
'reset warnings
-
DoCmd.SetWarnings True
-
-
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jayme |
last post by:
Is there a character limit for adding additional include
paths? After trying to add a path after a certain amount
(about 30 paths, about 2000 characters), it removes all
include paths from the...
|
by: Don Sealer |
last post by:
Is there a way to create a text box that can exceed the 255 character limit?
Thanks,
Don..........
|
by: @sh |
last post by:
Guys,
Working on a function to alert the user to too many characters being entered
into a text area, I've put together this function so far borrowing bits from
resource websites...
function...
|
by: pedalchick |
last post by:
I'm trying to build a mini-CMS for a friend (ASP.NET 2.0/VB) - just a
simple admin page w/ a text box and button that inserts some text into
a database (I'm using Access 2003 - text is going into a...
|
by: Penny |
last post by:
Hi all,
I've read that the message argument in SendObject allows a max of 255
characters. Is this correct? If so, doesn't this render SendObject severely
restricted as a way to send emails from...
|
by: kyjabber |
last post by:
I have a multi relationship database and I'm pulling the company's contact info, queryied if they are a grower, and a resulting list of their products by catgeory. I need to have the products listed...
|
by: senort01 |
last post by:
I am currently populating a word document with data from Access via VBA. I am, however, running into the character limit in the DocProperties that I am populating. Is there a way to override this...
|
by: senort01 |
last post by:
I am currently populating a word document with data from Access via VBA. I am, however, running into the character limit in the DocProperties that I am populating. Is there a way to override this...
|
by: postman |
last post by:
I'm using Access/VBA to traverse a file directory and produce a table of contents, including the keywords in the files' extended properties.
I've been able to successfully do this using the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |