Excel 2007 Print_Area annoyance

Has anyone experienced the joy of opening .xls file that worked fine in 2000, in Excel 2007, only to find they have a modal, seemingly unavoidable, unprogrammable dialog box block?

Don't pass go, don't collect 200 quid

Don't pass go, don't collect 200 quid

This is somehat of a reet PITA if you are automating opening a few thousand files looking for something and closing. And each one throws up this message, on open, but before the on open event, which doesn’t seem to accept sendkeys (or at least how could you program them?). And appears with no warning, and doesn’t seem to be a testable state (eg ready mode, edit mode, point mode, blocking the user for fun mode).

Anyone else seen this?

Anyone got a fix or workaround? (to allow the automation to continue, and to open the file, and close without saving changes)

I’m pretty sure I saw this years ago too, possibly with a 97 to 2003 move? I think in this current case the files were previously edited in 2000. I’d like to imagine that if I found a workaround last time around, a. I’d remember, and b. I’d know which dusty laptop to fire up and search. But I don’t, Have you got any ideas?

cheers

Simon

Advertisements

6 Responses to “Excel 2007 Print_Area annoyance”

  1. Harlan Grove Says:

    I’ve only seen this dialog when pasting from one workbook/worksheet into another when the copied range contains formulas that reference defined names. You don’t seem to be pasting ranges. Is this really popping up when you just open these files? Or are you doing other things you haven’t mentioned?

    You could try a kludge like having your base Excel application instance launch another Excel instance which it controls via Automation, then open these workbooks in the controlled instance. That should avoid defined name conflicts.

  2. Ross Says:

    I I’ve seen it, i think it happen with “Filter” as well or something, I’ve seen it some time when I’ve been automating a data filter and used a built in name.

    >I’d like to imagine that if I found a workaround last time around, a. I’d >remember, and b. I’d know which dusty laptop to fire up and search. But >I don’t, Have you got any ideas?

    It must be in your code library ;-)

    The only think I can think of is running the automation from a VB6/.net app, then using find windows to see if it’s popped up, then using send keys, or sub classing the form to send it a message – nice! If the code execution halts when the diolog pops up, you might even need to start a new thread before opening the file.

    That cant be the easiest way to solve it!!!

    What happen if you open it in compatibility mode, that’s to say have you tried opening in different ways?

  3. Patrick O'Beirne Says:

    For automating unattended runs of my ScanXLS spreadsheet on thousands of workbooks, I found the only way to get around some prompts was to run AutoIT and have it push the OK button for me. I can send you that script if you like.

    That prompt also happens with names like QTR1 that are now real cell addresses.

    It also happened with workbooks created in double-byte Windows locales being opened in XP / XL 2003 and the range names appear to be duplicated so one has to be renamed.

    Patrick

  4. Harlan Grove Says:

    Depending on what exactly is needed from these other workbooks, it might be expedient to use derived external references into CLOSED workbooks. I’d be very surprised if Excel 2007 choked on defined names when evaluating external references into closed workbooks. But if you need to check a lot of cells, this isn’t practical.

  5. Stephane Rodriguez Says:

    Remove the COUNTRY record from the BIFF file in question. It chokes with regional settings.

    Weird answer for a week-end, isn’t it? :-)

  6. Simon Says:

    Thanks for the suggestions guys
    I have some Excel manager code somewhere from a server project I did so I’ll resurrect that along the lines of Harlans 1st suggestion.
    Thanks for the AutoIT tip Patrick I had not seen that before, looks good.
    Stephane, if I can find a problem file on my system I will try your suggestion. It certainly ties in with my experience that a lot of these issues are files that have traversed locales.
    Ross – code library – good one ;-), actually my VB6 code is easy to find as its just text, its the VBA stuff locked away that I struggle to keep track of.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: