VideoHelp Forum
+ Reply to Thread
Results 1 to 5 of 5
Thread
  1. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    moving some excel files from office 2003 to office 2010

    so, we finally broke the 65536 row barrier in office 2003, excel. and i had to port the document over to office 2010. i have many problems with this move. mainly because all my data come from an access database that i maintain daily. my viewers want everything in excel, they want a plain viewer and excel is all they know..all they want to know. anyway.

    in ms access, i would highlight and copy to clipboard, the column(s) i needed and paste those updates into the excel sheets. its the fastest, simplest way to make such updates on a daily bases since the data was well under 65k, but now that we aquired new business, we added thousands more rows, nearly 67000. sometimes i just need to update one column. other times, two or more. it all depends what is updated with the latest info at the time.

    the only way i know how to update 67k rows in excel from ms access, is to select and copy 65000 rows from ms access and then paste that info excel, in two passes, and copy/past the remaining 2000 rows, and so on. of coursae, the data is growing each day, so 2000 will become 3000 and so on.

    does anyone have a better way to this ?
    Quote Quote  
  2. Member Cornucopia's Avatar
    Join Date
    Oct 2001
    Location
    Deep in the Heart of Texas
    Search PM
    Why aren't you using ODBC or similar to have Excel "view" the data that still actually resides in the Access db backend? Then, it could refresh manually and/or every time the spreadsheet opens. That also allows you to make use of the full rdbms & sql tools available to you, independent of the users' views.

    Scott
    Quote Quote  
  3. Banned
    Join Date
    Oct 2004
    Location
    Freedonia
    Search Comp PM
    Excel does have the ability to read in input separated by commas or other defined characters, but if you can use commas as your delimiter between column information (none of your data columns have data that uses commas), it may be the easiest for you to deal with. So the general procedure is to have some program, in your case Access, simply spit out comma delimited data and then open that in Excel. It's been a while since I've done this and I'm no Excel expert, but you can do what I do and search on the internet for a guide on how to have Excel open this kind of file. If you use commas as your delimiter, this is known as a .CSV file.
    Quote Quote  
  4. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    Why aren't you using ODBC or similar to have Excel "view" the data that still actually resides in the Access db backend? Then, it could refresh manually and/or every time the spreadsheet opens. That also allows you to make use of the full rdbms & sql tools available to you, independent of the users' views.
    odbc

    if i new how to do that, i probably would have tested that out as an alternative, but.. read on.

    i am not as savy in this area. i do not fully understand the ODBC thing. i am connected to the main system database (mckesson) through the back end tables via ODBC. but we are restricted/prevented from writing to any of the hundreds of tables that this beast entales. they have no db person, so i'm their guy. i can create almost anything via queries and some sql, thus, all the reports (excel files) i put out. however, our network system is ineffecient. it takes over a minute to open most of the large excel files. nobody (IT) knows what they are doing i guess, because they can't seem to fix that ever since we all upgraded our systems and pc's, including office 2003 to office 2010 in the last four years. i keep office 2003 as my main computer because it gives me the least trouble and is all i really need. plus, the main reason i keep it is because of the database. m$ changed the interface and i am not as productive at work in that 2010 interface. the db view for the tables and queries do not sort the same way. everything is pulldown/tree-view. i need and survive using the old style interface of office 2003 for ms access.

    csv files.

    i know how to open .csv files. when they are greater then 65k, i import them into access and deal with them there and give a sub-set of data, after i've worked them up a bit.
    Quote Quote  
  5. Member vhelp's Avatar
    Join Date
    Mar 2001
    Location
    New York
    Search Comp PM
    my apologies if i confused anyone earlier. i got ahead of myself in my last response. and later on, i had time to rethink the problem and came up with a temporary fix.

    so the issue is with copying rows greater than 65536 in access 2010 and pasting into excel 2010.

    the method fails when selecting and copying to the clipboard, over 67000 rows in access 2010 for pasting into excel 2010. it seems odd (maybe a bug in access 2010) that it still imposes a 65536 copy row limit to the clipboard when the data is attempting to be pasted into excel 2010. its odd because i can copy far greater rows in access 2003/2010 into itself with no trouble. same for copy/pasting excel 2010 -to - excel 2010.

    then i came up with a work-around until something better comes along, using two queries. the first query selects up to 65000 rows. i copy and past that data into the excel sheet, and the second query selects the remaining rows, in this case, 2000 rows, and again, i copy that and paste into the excel sheet. since i use line numbers, that is the criteria for counting the rows.
    Quote Quote  
Visit our sponsor! Try DVDFab and backup Blu-rays!