Circles in Excel print oval?!? (Excel NT4 & Win2000)

Topic

New Reply

Hello All,

I have been having this problem “forever” but I finally need to fix it.

The problem is that “perfect circles” print as flattened ovals.

I’m looking for a solution that allows the circles to look normal on the worksheet when viewed on a monitor and print as circles on a printer.

I don’t think this is a simple “you don’t have the latest printer drivers” issue.

I can draw the circles in Excel, holding the shift key so they are “perfect”, or I can draw them in another program like Word and then paste them into any Excel worksheet, but either way they print as flattened ovals (shorter than they are wide). They look flattened in the print preview. Autoshapes that are circular exhibit the exact same problems.

This happens to the same degree no matter which of the three printers I have access to I print to, an HP LaserJet 5000N, an HP Color LaserJet 5M or an Epson Color Stylus 3000. I know I have the latest drivers for the Epson, but I have the “company standard” drivers for the other two. This problem only occurs when printing from Excel, any other program that prints circles (Access, Word, PowerPoint, Internet Explorer, Photoshop) prints them perfectly.

This happens to other folks too. I’ve started a thread in the CompuServe MS Office Help Forum, and other folks are noticing the same problem. One interesting observation that those folks are making is that the degree of flattening changes with the font being used. (I’m not sure if they mean changing the default font or just the font on the worksheet.) The folks that have access to more than one printer also seem to have the problem no matter what printer they print to.

The latest theory over on the CI$ forum is that Excel is storing the position of the circle using cell references (like the top is on cell XX, the bottom is on XX, the left side…) and that Excel does not print its cells with the same proportion that it displays them on the screen, so when it distorts the cells, the circle gets distorted along with them.

Any ideas? Suggestions? Actual solutions?!? I know I can play with the shape on the worksheet, stretching it taller and taller until it prints as a circle, but that won’t work for me, these worksheets are viewed with a monitor 75% of the time, but often printed to be taken to a meeting, faxed or bound for storage. We are looking or a solution that will allow the circles to look correct on the screen and print correctly too! Is that so much to ask?

Thanks for listening! – Thomas

Reply | Quote

Replies

I recall an identical discussion over at the MSOFFICE forum of WUGNET:

http://go.CompuServe.com/MSOfficeForum?Loc=US%5B/url%5D

Haven’t found another solution than the (feeble) ones you got over there. I guess a macro that replaces the circles on screen with deliberate ovals that print as circles and puts the circles back after the print is your best shot.

Reply | Quote

You’ll love this ‘solution’:
Insert your Excel spreadsheet as a worksheet object into a Word document, making sure to re-set the scaling (and turn off ‘lock aspect ratio’) and print it from there.
HTH

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hey HTH / macropod!

If that works, it will be the best solution yet. Thanks a lot! I’m not sure I can write a macro to do what Jan suggested, so this looks more hopeful!

Does MS care that one cannot print circles from Excel? Is there a viable way to report the problem to them?

Thanks again for the solution suggestion, I’ll try to find time today to try it and I’ll post here what the result was…

– Thomas

Reply | Quote

You would typically use the Workbook_beforeprint event for that.

Lets say you name the circles that show on screen ScreenCircle1, ScreenCircle2, etcetera and the printing circles PrinCircle1, PrintCircle2, etc.

Put this code in the Thisworkbook module:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ShowProperCircles True
Application.OnTime Now, “ShowScreenCircles”
End Sub

And this in a normal module:

Option Explicit

Sub ShowProperCircles(bPrint As Boolean)
Dim oCircle As Object
For Each oCircle In ActiveSheet.Shapes
If InStr(oCircle.Name, “Print”) > 0 Then
oCircle.Visible = bPrint
ElseIf InStr(oCircle.Name, “Screen”) > 0 Then
oCircle.Visible = Not bPrint
End If
Next
End Sub

Sub ShowScreenCircles()
ShowProperCircles False
End Sub

Reply | Quote

Hello HTH / macropod,

Uh, HOW do I insert the Excel worksheet as a worksheet object into a Word document? I’ve tried just choose the Object… from the Insert menu in Word, but when I browse to the Excel workbook and select it, only the cover sheet (the first of many sheets in the workbook) gets brought to the Word doc.

I even thought of moving to the sheet in the workbook that has the circles on it, which gets closer, but I still only get the first part of that sheet (like the first page’s worth) I don’t get the part of the worksheet (which is further down on the sheet) that has the circles.

Also where would I be finding the “scaling” to reset and the “lock aspect ratio” to turn off? In the abortive attempts described above I looked for these settings but couldn’t find them in Word.

Thanks for listening! – Thomas

Reply | Quote

(Edited by macropod on 23-Aug-02 09:09. The answer given about sizing/scaling an object is for Word 97, Word 2000 (and probably other later versions) has a different set of dialogue boxes & syntax for changing an object’s size.)

Hi Thomas,

When you insert an Excel file via Insert|Object|Create from File, the whole file is inserted. What you should see is a ‘table’ with gridlines, representing part of the last active worksheet in the Excel file, but possibly with more/less rows & columns displayed.

If you double-click on the table, Excel will open it within the Word document. You can then navigate to whatever worksheet & range you want to see. Put the top-left cell of the range you want to see in the top left corner of the embedded Excel object.

While you’ve got the Excel object open, you’ll also see that there are small black ‘handles’ on the edges and corners for re-sizing. These change the number or rows/columns that are visible. You can’t have more rows/columns than will fit on one page (but you can change how many with scaling – see below) and, depending on formatting, sometimes less.

If you need multiple parts of your Excel file displayed, you’ll have to use multiple instances of it in Word. This increases the size of your Word file by about the size of the Excel file each time. A better approach, especially if the Excel file is large, might be to create Excel objects for just the portions you need, via Insert|Object|Create New, and paste in only the portions you need. Hint: you can then also put formulae into the new objects that link to the original Excel files, so that the embedded objects can be updated without having to re-paste the data or re-insert & manipulate the objects each time.

Once you’ve got the area you want displayed showing, click outside the Excel object to de-activate it. Now (in Word) click on it (once) and select Format|Object|Size. Uncheck “Lock aspect ratio”, then click “Reset”, then click “OK”. Now the Excel object will be correctly scaled.

If the Excel object is too large/small, click on it (once) and drag the corners whichever way you need to get the correct size. Alternatively, you could do this via Format|Object|Size, recheck “Lock aspect ratio”, then change any one of the dimensions, then click “OK”.

Getting this all set up can take a bit of effort first time through, but then you’ll be able to have Word reports with embedded Excel tables that can be easily maintained and will output their own embedded objects correctly scaled.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Another method which looks as if it will work is to take a “Picture” of the circle and print the picture rather than the origial. In my printout this picture looks to be a true circle but I don’t have a compass to check it with

HTH

Peter

Reply | Quote