Automatic Delphi Response to an EXCEL OnScreen Request 1

[BillKilgore]

Hi Everyone,

I've got an application that upgrades several Excel worksheets in separate files. Each file is a standalone. Each utilizes some basic values that must be present in each of the standalone file. Each of these values requires manual updating by the user. I would like have each of the standalones automatically be updated from a central file that is one of yet another set of Excel files and contains the basic values required.
The snag I've hit is that as Delphi opens each of the standalones Excel requests, with a Yes or No button in a box, if the user would like to update the standalone from the remote central file. A manual keyboard/mouse input is required from the user before the application will resume.
I want to have the application answer 'Yes' to the each Excel query automatically.
I've looked over the TBasicAction Class was unable to find something that would work. I've also tried to WRITELN a Carriage Feed (ASCII #13) to the Console but couldn't get it to compile.
I've also considered having the application open a flat file containing the values but those values are derived from another set of Excel files so the intermdiate flat file seems like an extra step and less elegant.
Have any of you out there have an answer to this approach?

Thank you in advance, Bill K.

 

[Griffyn]

Are the files linked using the External Data option? Or the simple cell link option? eg. ='[c:\Book1.xls]Sheet1'!C4

For me, in Excel 2003, I get no prompt when I open a workbook with a simple link, even if the dependent workbook is not open. And the value is up to date.

Actually, from reading your question again, it sounds like the excel files aren't linked together at all, and you're using your Delphi app to synchronise values between them. Perhaps the simple link example above will be useful?

 

[smays]

Bill K.


Perhaps I am missing your point. If I am, please forgive me.


It sounds like you have several Excel spreadsheets you are trying to update individually with the same piece of information. Perhaps when you try to close the spreadsheets in code - or perhaps when you try to File|Save the spreadsheet in code - you receive a message box of some flavor, perhaps from Excel.exe, asking the user if he/she wants to save the file or something? If I am correct in my assumption, you can temporarily disable the 'Alerts' by setting the "DisplayAlerts" property to false. Please consider the following code:

varMSExcel.OlePropertySet("DisplayAlerts", bPromptToSave);


Forgive me, I am a C++ guy (C++ Builder). Nevertheless, I believe the above code is proper PASCAL.


When bPromptToSave is true, the user will be prompted to save by Excel.exe, whether the spreadsheet is an existing spreadsheet or if a new spreadsheet is created. When bPromptToSave is false, the user will NOT be prompted to save... This only works once every time your code does something that would cause Excel to prompt you to save.


Steve.

 

[DjangMan]

How are you connecting to Excel?

 

[BillKilgore]

I probably should have mentioned that the Excel edition in question is part of my Office 2000 suite. I know it's a little long in the tooth but it still serves us well. I should also indicate that we're using the simple data link between worksheets. The snag here is when the standalone sheet tries to access the other sheet Excel puts up a box with the following message;

"The workbook you opened contains automatic links to information in another workbook.
Do you want to update this workbook with changes made to the other workbook?"

accompanied by a "Yes" and a "No" button.

All that is required by is that one of the buttons be tapped and the software continues with its task.

I would like to automatically show Yes to Excel's message with out having to tap the Yes botton for each occurance.

When the software completes each stand alone sheet it is saved, closed, and the next sheet is addressed. Saving the stand alone files is not a problem.

 

[smays]

Are you using OLE automation? Can you try setting "DisableAlerts" to true?

Steve.

 

[DjangMan]

http://support.microsoft.com/kb/826921

 

[DjangMan]

Oops - that link says it is for Excel 2002 and later...

 

[BillKilgore]

DjangMan, you're beautiful. The problem is solved. I went to the Microsoft link you included and it does, indeed, work for Office 2000. It was a simple check mark. Thank you very much.

Bill K.