VideoHelp Forum

Our website is made possible by displaying online advertisements to our visitors. Consider supporting us by disable your adblocker or Try ConvertXtoDVD and convert all your movies to DVD. Free trial ! :)
+ Reply to Thread
Results 1 to 7 of 7
Thread
  1. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    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 ?
    Last edited by vhelp; 30th Mar 2016 at 19:13.
    Quote Quote  
  2. Member Cornucopia's Avatar
    Join Date
    Oct 2001
    Location
    Deep in the Heart of Texas
    Search PM
    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
    Quote Quote  
  3. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    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.
    Quote Quote  
  4. Member Cornucopia's Avatar
    Join Date
    Oct 2001
    Location
    Deep in the Heart of Texas
    Search PM
    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
    Quote Quote  
  5. 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.
    Quote Quote  
  6. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    solved

    I have solved it and
    Quote Quote  
  7. Originally Posted by vhelp View Post
    solved

    I have solved it and
    You are so smart! Congratulations!
    Quote Quote  



Similar Threads