none
Excel 2007 docs drop "print area" info when opened from OLE+Excel2007 RRS feed

  • Question

  • Main facts:

    -I have hundreds of excel docs to print, they were mainly created with Excel2003, but now it has been replaced with Excel2007 (is the only one installed now) so some of them are now Excel2007 documents.

    -All of them have a print area manually defined.

    -If you open one manually (double click) you can check print area is there, and if you print it, it does ok (2003 or 2007 formats).

    -But, if you open one of them with 2007 format via OLE automation (from Delphi in this case, but same issue has been reported using VB6 or C++) and having Excel2007 installed too, the print area is LOST, not even defined.

    -After opening a 2007 formated doc via OLE+Excel2007, print area is not defined, but a named area is  present, called "Print_Area", empty values, referencing the right area, but it is not used as the real print area.

    -Opening a 2003 document via OLE (using Excel2007) doesn't mess with the print area and it is all OK with it.

    -If now you manually open the 2007 format doc, save it as excel 2003, and try it all again via OLE, print area is conserved and used (but documents looks different as 2007 doesn't properly convert to/from 2003, ouch!).

    -If you decide to revert to Excel2003, all docs manipulated with Excel2007 are not accesible now (ouch again!).

    Some light on this issue anywhere? Any solution I could try out?

    More info:

    -No macros, no external links, no OLE post-processing, just open and print the active sheet.

    -This same issue was reported -but not cleared- here and there:

    https://social.msdn.microsoft.com/Forums/office/en-US/534f8548-4ab3-4932-8019-769b1074921c/information-of-print-area-is-missing-in-vb6-addin-of-version-excel-2007-and-excel-2010

    https://www.autoitscript.com/forum/topic/133810-_excelbookopen-2007-xlsm-file-print-area-gets-lost/

    -Excel has other differences when used from OLE, it doesn't work the same way: "typing" a date on a cell from OLE will try to use american date format on it, regarless of locale info, so "1/5/2015" will go to january (american format) instead of may (european format, used in locale), but this one is not so important and you can live with it (once you know about).

    Regards,

    Sergio Hernandez.


    Thursday, June 25, 2015 10:56 AM

All replies

  • Hi Sergio,

    Base on my test with this code (Excel object model), it works fine.

    Dim oExcel As Excel.Application
    Dim oWb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set oExcel = New Excel.Application
    Set oWb = oExcel.Workbooks.Open("XXX")
    oExcel.Visible = True
    Set ws = ActiveSheet
    MsgBox ws.PageSetup.PrintArea
    Set oWb = Nothing
    oExcel.Quit
    Set oExcel = Nothing

    How do you open excel file via OLE? Please provide your detail code.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 2:52 AM
    Moderator
  • Hi Starain, thanxs for posting.

    I do open the document in the exact same way as you do, no change here, just calling the commands form another languaje.

    The problem, as I have detected today, can be related to the print area internal name when translated to other languajes, surely, if you use "english version" of excel for all your test you won't detect it.

    When Excel2007 "Spanish version" opens a 2013 format document (also saved with a 2013 spanish excel), print area is defined ok, and if you inspect named objects, there is an area defined with the name "Área_de_impresión" ("Print area" in spanish), but, when you open a 2007 format one, the name is now "Print_area" and this is not detected as the real print area, just as a custom named range.

    I think this is the same problem I commented about typing dates from OLE connections: Excel changes its mind from english to spanish "locale" in a quite inconsistent way: Print area range is NOT converted to the correct naming when E2007 opens a doc saved by itslef usign its own native format, if you do it via OLE, but does it ok when done manually by doble clicking it, or surprisingly, when the doc was saved on an older format like 2003.

    So may be you need a copy of excel 2007 (or 2010 as others report) on another languaje to properly test it, as it dependes on automatic naming changes (something I personally and deeply hate).

    Friday, June 26, 2015 9:54 AM