none
Using Excel.Sheet.Shapes - generated output varies with Office version RRS feed

  • Question

  • Hi Folks

    Have you noticed that the co-ordinate system is not consistent across different Office versions (or is the OS ?). I developed some code that draws some line figures on an excel sheet. The positioning of these figures is critical to align with some formatted cell text. Then on my new PC with Win8, I ran the exact same code - but the figures where cast slightly left and higher than on my previous system. Although I installed my same 2007 Office on my new system, I noticed that I already had another version of Office already on my system.

    This is a follow on from this thread in the VB forum. http://social.msdn.microsoft.com/Forums/vstudio/en-US/da1a4b92-0a29-47f5-a402-d3234aea44ac/vb-generated-excelshapes-output-varies-with-os?forum=vbgeneral

    Would love to hear of your experiences on this issue, to confirm or deny that it is due to incosistent co-ords in the Office versions.

    Thank you.


    Leon Stanley - - Hymn#263 [Courage, Brother]♪Heed no custom, school, nor fashion; Trust in God, Trust in God, Trust in God and do the right.♪ Norman Macleod 1857

    Thursday, June 12, 2014 9:16 AM

Answers

  • The code in your other thread doesn't give any clue about where you are adding shapes in relation to cells and borders.

    For shapes the coordinates are in points from the top left corner of A1. The default height and width of cells, and hence borders, depends on the Normal font; if not the same in different systems the position of your shapes positioned with given points in relation to cell borders will indeed change. From memory in different old Excel versions there was a slight difference in cell padding, though you say you are using 2007 in both systems so not that. 

    Also, shapes are positioned to exact pixels, the default in Windows systems being 0.75 points = 1 pixel, maybe there's a difference in your respective systsms.

    If the objective is to position your shapes relative to cell borders work with something like this -

    myTop = Range("A4").Top 
    (of course could use the given row/column's .Left or .Top)
    • Marked as answer by LeonCS Wednesday, June 18, 2014 7:42 AM
    Thursday, June 12, 2014 2:40 PM
    Moderator

All replies

  • The code in your other thread doesn't give any clue about where you are adding shapes in relation to cells and borders.

    For shapes the coordinates are in points from the top left corner of A1. The default height and width of cells, and hence borders, depends on the Normal font; if not the same in different systems the position of your shapes positioned with given points in relation to cell borders will indeed change. From memory in different old Excel versions there was a slight difference in cell padding, though you say you are using 2007 in both systems so not that. 

    Also, shapes are positioned to exact pixels, the default in Windows systems being 0.75 points = 1 pixel, maybe there's a difference in your respective systsms.

    If the objective is to position your shapes relative to cell borders work with something like this -

    myTop = Range("A4").Top 
    (of course could use the given row/column's .Left or .Top)
    • Marked as answer by LeonCS Wednesday, June 18, 2014 7:42 AM
    Thursday, June 12, 2014 2:40 PM
    Moderator
  • Thanks Peter for you comments

    My same code also formats the entire excel sheet, sets the font as calibri, and also generates the line drawings. If on my old PC i generate a excel sheet.xlsx with this code, then copy that file to my new PC, it displays perfectly on my new PC. It is only when I generate that exact same file on my new PC - then it displays differently.

    I have overcome this hurdle by adding a 'calibration' function in the app which allows the user to make adjustments so that is displays as it should.

    Let me investigate Excel.Range().Top function that you have brought to view.

    Thanks


    Leon Stanley - - Hymn#263 [Courage, Brother]♪Heed no custom, school, nor fashion; Trust in God, Trust in God, Trust in God and do the right.♪ Norman Macleod 1857


    • Edited by LeonCS Monday, June 16, 2014 8:35 AM
    Monday, June 16, 2014 8:35 AM