none
Microsoft Excel Printing Cutting off cell text

    Question

  • Hello, 

    I have a couple of excel documents that don't want to print properly for some of my clients. They are old 2003 excel documents that are being used with excel 2010, and when they go to print them, some of the cells with text in the documents get cut off by other cells. This shows up in the print preview, and printing, but not when modifying the document.

    I've tried copying and pasting the data to a new sheet, I've tried setting Autofit settings, changing the font, enabling word wrap, merging cells, and none of those would work. 

    Any recommendations on other fixes I could try?


    -- Douglas

    Thursday, February 14, 2013 2:29 PM

Answers

  • Put your 500 files into a folder, change the path name in the code to the path where you stored the files, and run this macro. I have assumed that none of the files have macros (because I am not copying the macros over into the new workbooks, but it is possible to do that, just different code required). the new files are stored in the new folder Converted. If there are linking formulas (formulas that reference other workbooks or worksheets) then the code should be different as well, but this will work for very simple workbooks. In fact, there are a lot of issues that this doesn't address - chart, objects, etc. so it is restricted to very simple workbooks, but, you know, it's a start at fixing your specific printing problem. (for all those other things, I would have just opened the file and saved it as a macro enabled 2010 workbook).

    Sub Copy2003To2010Files()
        Dim myPath As String
        Dim myNPath As String
        Dim myB As Workbook
        Dim myNB As Workbook
        Dim i As Integer
     
        Application.DisplayAlerts = False
        Application.EnableEvents = False
     
        myFile = ActiveWorkbook.Name
     
        myPath = "C:\Documents and Settings\techadmin\My Documents\Excel\Delete These\Convert\"
        myNPath = myPath & "Converted\"
        MkDir myNPath
        WorkFile = Dir(myPath & "*.xls")
        Do While WorkFile <> ""
            Application.StatusBar = "Now working on " & WorkFile
     
            Set myB = Workbooks.Open(myPath & WorkFile)
            Set myNB = Workbooks.Add
            For i = 1 To myB.Worksheets.Count
                If myNB.Worksheets.Count < i Then myNB.Worksheets.Add after:=myNB.Worksheets(myNB.Worksheets.Count)
                myB.Worksheets(i).Cells.Copy myNB.Worksheets(i).Cells
            Next i
        myNB.SaveAs myNPath & Replace(myB.Name, ".xls", ".xlsx"), FileFormat:=xlOpenXMLWorkbook
        myB.Close False
        myNB.Close False
        WorkFile = Dir()
    Loop
        Application.StatusBar = False
        Application.EnableEvents = True

    End Sub




    Friday, February 15, 2013 6:01 PM

All replies

  • Try this:

    Create a new 2010, then copy and paste values to a sheet in the new workbook. Then format the cells within 2010.

    Thursday, February 14, 2013 4:30 PM
  • That does work and seems to fix the issue.

    Any quick solutions or tips on how to do this across roughly 500 excel documents? Or am I going to have a long day :-p.


    -- Douglas

    Friday, February 15, 2013 3:47 PM
  • Put your 500 files into a folder, change the path name in the code to the path where you stored the files, and run this macro. I have assumed that none of the files have macros (because I am not copying the macros over into the new workbooks, but it is possible to do that, just different code required). the new files are stored in the new folder Converted. If there are linking formulas (formulas that reference other workbooks or worksheets) then the code should be different as well, but this will work for very simple workbooks. In fact, there are a lot of issues that this doesn't address - chart, objects, etc. so it is restricted to very simple workbooks, but, you know, it's a start at fixing your specific printing problem. (for all those other things, I would have just opened the file and saved it as a macro enabled 2010 workbook).

    Sub Copy2003To2010Files()
        Dim myPath As String
        Dim myNPath As String
        Dim myB As Workbook
        Dim myNB As Workbook
        Dim i As Integer
     
        Application.DisplayAlerts = False
        Application.EnableEvents = False
     
        myFile = ActiveWorkbook.Name
     
        myPath = "C:\Documents and Settings\techadmin\My Documents\Excel\Delete These\Convert\"
        myNPath = myPath & "Converted\"
        MkDir myNPath
        WorkFile = Dir(myPath & "*.xls")
        Do While WorkFile <> ""
            Application.StatusBar = "Now working on " & WorkFile
     
            Set myB = Workbooks.Open(myPath & WorkFile)
            Set myNB = Workbooks.Add
            For i = 1 To myB.Worksheets.Count
                If myNB.Worksheets.Count < i Then myNB.Worksheets.Add after:=myNB.Worksheets(myNB.Worksheets.Count)
                myB.Worksheets(i).Cells.Copy myNB.Worksheets(i).Cells
            Next i
        myNB.SaveAs myNPath & Replace(myB.Name, ".xls", ".xlsx"), FileFormat:=xlOpenXMLWorkbook
        myB.Close False
        myNB.Close False
        WorkFile = Dir()
    Loop
        Application.StatusBar = False
        Application.EnableEvents = True

    End Sub




    Friday, February 15, 2013 6:01 PM
  • Thanks!

    I tried out the macro and it works beautifully for what I need!!


    -- Douglas

    Monday, February 18, 2013 5:33 PM
  • Thanks to both of you to share the helpful code to us. This will help the others who meet the same issue.

    Jaynet Zhang
    TechNet Community Support

    Friday, February 22, 2013 8:24 AM
    Moderator
  • I'm glad you sent a message on this, I found that the issue is also related to drivers.

    The printer was an HP Laserjet 8150, and the issue occured with HP's Universal Print Drivers and the 8150 PCL6 drivers, but the issue was not occuring with PCL5 drivers. So a quick fix would also be too set up another printer with PCL5.

    It did occur on multiple printers with the Universal printer drivers, and pcl5 drivers though.


    -- Douglas

    Friday, February 22, 2013 2:05 PM