A normal copy/paste will not hold more than 255 characters in excel from access. However, if you do a paste special and choose either unicode, text or CSV as the data type then it will take it ALL in. The Biff5 paste is the best looking option because it allows easier row/column auto resizing but that option takes only the dreaded 255.
You see some fields have 400 or so charcetrs while others have 5 or so, auto fitting is nice to have. I made a macro button that autosizes all the cells on any data but when the paste special is used with anything other than Biff5 my macro just makes each column extra long and the row extra thin so the data-cells get extremly elongated. I am thinking i can just change my macro from an autosizing of the rows/columns to a manual set width (ie column sizes) macro, then wrap the text and auto size only the rows. Should work in theory (haha...heard that one before).
So now the part that needs some work is creating something that after the query creates a new table in access will copy the table and then paste special into an excel file. I could keep the generated query table open and do the output to excel to get the appropriate files open. So all i would need is some kind of code that says:
active table in access, select all, copy
active table in excel, paste special, unicode or the other ones
I could open/close the table, my query, the excel file for output with a simple macro command in access, it is just those few operations from access that i need. if only there were a macro recorder in access. I realize this willl probbaly take OLE code but can it be that bad since i can do most of the opening/closing files from an access macr, right? well what do you think, can it be done?
You see some fields have 400 or so charcetrs while others have 5 or so, auto fitting is nice to have. I made a macro button that autosizes all the cells on any data but when the paste special is used with anything other than Biff5 my macro just makes each column extra long and the row extra thin so the data-cells get extremly elongated. I am thinking i can just change my macro from an autosizing of the rows/columns to a manual set width (ie column sizes) macro, then wrap the text and auto size only the rows. Should work in theory (haha...heard that one before).
So now the part that needs some work is creating something that after the query creates a new table in access will copy the table and then paste special into an excel file. I could keep the generated query table open and do the output to excel to get the appropriate files open. So all i would need is some kind of code that says:
active table in access, select all, copy
active table in excel, paste special, unicode or the other ones
I could open/close the table, my query, the excel file for output with a simple macro command in access, it is just those few operations from access that i need. if only there were a macro recorder in access. I realize this willl probbaly take OLE code but can it be that bad since i can do most of the opening/closing files from an access macr, right? well what do you think, can it be done?