none
Copy Excel worksheet to another workbook with Access VBA code. Text is truncated.

    Question

  •  

    Hi, I try to write the below VBA code to copy worksheet from one workbook to another workbook, but I found some of the text in the cell has been truncated. If I just do the same thing in Excel, it works no problem. Anybody could give me some idea or solution? Thanks in advance for your help.

     

    VBA Code

    Workbooks(book1).sheets(1).Copy after:=Workbooks(book2).sheets(1)

    Friday, August 10, 2007 3:50 PM

All replies

  • I'm guessing here. More information on what got truncated would assist (with examples of before and after). What version of Excel are you using?

     

    My guess is that you have cells in the first book which contain more than 255 characters of text and/or formula, and that in the copied version of the sheet, those cells have been truncated to about 255 characters. Previous versions of Excel could only handle 255 characters of text in a cell. Excel 2003 (and I don't know which others) can handle more than that, but I think there may still be some things which haven't caught up. Perhaps the VBA Copy method is one of them. If that is the case, I don't know if there is a neat solution.

    Also, if you're pasting into an Excel workbook which has been saved as a backwards compatible version (for example saved as type "Microsoft Excel 5.0/95 Workbook"), then I suspect it's not possible to store more than 255 characters in a cell.

    Sunday, August 12, 2007 7:12 AM
  • This is fixed in 2007 when you copy a sheet
     
    If you use a older version you must copy the sheet first with the code and after that the cells from the source to the Dest sheet to see all 255 characters

    --

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm
     
     
    "Cringing Dragon" wrote in message news:9204f12d-edd4-412f-be39-b065008fa708...

    I'm guessing here. More information on what got truncated would assist (with examples of before and after). What version of Excel are you using?

     

    My guess is that you have cells in the first book which contain more than 255 characters of text and/or formula, and that in the copied version of the sheet, those cells have been truncated to about 255 characters. Previous versions of Excel could only handle 255 characters of text in a cell. Excel 2003 (and I don't know which others) can handle more than that, but I think there may still be some things which haven't caught up. Perhaps the VBA Copy method is one of them. If that is the case, I don't know if there is a neat solution.

    Also, if you're pasting into an Excel workbook which has been saved as a backwards compatible version (for example saved as type "Microsoft Excel 5.0/95 Workbook"), then I suspect it's not possible to store more than 255 characters in a cell.


    Regards Ron de Bruin http://www.rondebruin.nl/tips.htm
    Tuesday, May 25, 2010 4:57 PM