copy/paste columns > than 65k from ms access into excel 2010...[solved]

[vhelp]

solved - see post # 6

Hi all.

I am trying to learn if there is an easier way to copy (or transfer) column(s) greater than 65k (65535) from ms access into excel, like a switch or flag in ms access. Yes, it is possible to copy greater than 65k cells while inside excel, but you can't copy 65k+ from access to excel--from access, you have to export that to a file and import it into excel, but that seems a bit tedious in steps. I am wondering if there is a more quicker way of copying large columns from ms access into excel and with the least amount of steps.

Does anyone have any suggestions office 2000/2003/2010/2013 ?

[Cornucopia]

Why are you exporting/importing, or copying/pasting? Why not just use ODBC to natively use access tables/queries AS your direct data source in excel?

Scott

[vhelp]

Its not that kind of situation.

When I copy data into excel, it is for quick formatting, searching, testing, or some form of debugging that I need to do to that column. Sometimes I re-insert that column back into access, and sometimes I don't. It all depends on what I am doing at the time, and I'm in a rush to do it. So I need to do it quick w/out thinking too much into it. You know.. get in and out. These are things that I can't easily do in access to one column of data. Its much easier to throw a column into excel and do a few quick things to it to get ideas or fix something or whatever. This is what i've been doing for years, but now our data has grown well past the 65k limit in access and the queries that run I can't easily take columns and jump out/in with them when debugging. Exporting and Importing is too labor'us a process for me and I am now searching for another method that I hope is quicker. Given the above discussion, doing an ODBC is not practical.

[Cornucopia]

My suggestion would be then to either:
1. Learn to do your "quick & dirty" stuff within the database (query language is about as full-featured as excel's formula language),
or
2. Upgrade from Access to something like SQL Server, where you don't have near the limitations.
(or both)

Scott

[ackboo]

I digged a bit in google, apparently this a windows clipboard limitation. Access can't copy more than 65000 lines into the clipboard. It caused problems even when exporting to a new excel file from within access unless you uncheck "export data with formatting and layout" which bypasses the windows clipboard.
People also use VBA scripts to bypass the clipboard.
http://stackoverflow.com/questions/3209974/how-to-transfer-more-than-65536-rows-from-a...-to-excel-2007
http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/cant-export...b-280c82094bce

Not sure if that solves your problem, but you can export directly to an excel file.

[vhelp]

solved

I have solved it and

[pandy]

solved

I have solved it and

You are so smart! Congratulations!