473,327 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Exporting query results to excel max 255 characters

Hello Everyone

I've built a database using Access 2000 which includes a query which is
built using a form containing filters. No problem. When I export the
results of the query to excel, (File > Export > Save as type: Microsft
Excel 97-2000) one of the fields, which is a memo field type, loses any
data over the first 255 characters. How do I get all the data into
excel?

Thanks
Suffrinmick

Nov 13 '05 #1
1 7731
Suffrinmick wrote:
Hello Everyone

I've built a database using Access 2000 which includes a query which is
built using a form containing filters. No problem. When I export the
results of the query to excel, (File > Export > Save as type: Microsft
Excel 97-2000) one of the fields, which is a memo field type, loses any
data over the first 255 characters. How do I get all the data into
excel?

Thanks
Suffrinmick


You will probably need to do some VBA trickery to do it instead.

Here's the DAO version (note the serious lack of any error checking or
object reference cleanup, and it's completely untested, as it's off the
top of my head, but it's in the ballpark...)

'make reference to the Excel library...
Public Sub ExportToExcel(rs as DAO.Recordset)
dim xl as Excel.Application
dim xlWB as Excel.workbook
dim xlWS as Excel.Worksheet
dim xlRng as Excel.Range

set xl = new Excel.Application
set xlWB = xl.ActiveWorkbook
set xlWS = xlWB.ActiveSheet
set xlRng = xlWS.Range("a1")

dim r as integer, c as integer

'If you're using Excel 3/4, need to make sure that rs
' doesn't have more than 16K records!
rs.movelast
rs.movefirst
c = rs.fields.count
r = 1
while not rs.EOF
for x = 0 to c - 1
xc = x
if rs(x).type = "Memo" then
xc = 0
xs = rs(x).text
while len(xs) > 255
xc = xc + 1
xlRng.offset(r, xc).text = left$(xs, 255)
xs = mid$(xs, 256)
wend
else
xlRng.offset(r,xc).value = rs(x).value
end if
r = r + 1
wend
'put code here to save workbook, with a file name, of course.
'xlWB.Close

end sub

I did the reverse in an old job from within Excel and ADO. ADO
recordsets are column-dominant, oddly enough. There is a quite powerful
function to suck the contents of an ADO recordset into an Excel Array,
but for it to make it fit most people's expectations of the data
(row-column), the array needs to be transposed before using another
quite powerful function that pushes an array into a Worksheet range.
These two functions so completely obliterate walking the array to do it
it's not even funny (speed difference similar to writing a screen
clearing routine on a C64 in BASIC via PEEKS and POKES vs coding it in ASM).

Why, after all these years though, have the Excel-Access transporters
been SO frustratingly impossible to configure???

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: G | last post by:
When I export data from access to excel by with "export" or "Analyze with" I seem to loose parts of some fields (long text strings). Is there a way to export it all to excel? Thanks G
2
by: Kenneth | last post by:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
3
by: BarbaraAnne | last post by:
Hello I have almost successfully written VBA code to export the results of a query to an Excel file, however, one of the fields is very long, and gets truncated to 255 characters. If I manually...
12
by: TARHEELS721 | last post by:
I am trying to send the results of a query that runs when I click a button on my form that is based on a parameter query. The code runs without any errors but nothing is exported into my excel...
4
by: myemail.an | last post by:
Hi all, I use Access 2007 and have the following problems: when exporting banal select queries (either to Excel or to a csv file) I find that exporting often doesn't work and creates a file with...
7
by: alf8kitty | last post by:
Hello, Im still very new to php and am having a problem. I return a MySQL query to a form in my php page and I want to be able to export the form data to Excel when the users clicks a link (or a...
3
by: Tempalli | last post by:
I am exporting the data from ms access to excel where the error displays as Run-time error -2147467259(800004005) Method 'Copyfromrecordset' of object 'Range' faild. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.