By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,852 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

Exporting query results to excel max 255 characters

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.