Open File popup

[Catherine Finnegan]

Is there some way to disable or intercept the Open File Dialog box??
My users have spreadsheets stored in a Lotus Notes Database that are
linked to external files. I programmatically deattach the
spreadsheets from the database and then, using OLE, I print them out.
I would like this entire operation to be unattended, but with that
dialog box popping up each time I try to print a spreadsheet that
contains an external reference to a file someone is required to be at
the server.

TIA,

Cathy Finnegan.

[Jim Rech]

Perhaps unchecking "Ask to update automatic links" under Tools, Options,
Edit will help (assuming it's selected now). Of course if the referenced
files are not available you'll get an error message.

--
Jim Rech
Excel MVP

[Tom Ogilvy]

If you are opening them with a program, look at the updatelinks argument to
the Workbooks.Open method. If this is not the problem, post a couple of
lines of code to show how you are opening the files.

Regards,
Tom Ogilvy

Catherine Finnegan wrote in message
<39fd9skj50dviu1kr...@4ax.com>...

[Catherine Finnegan]

Tom:

What happens is that my users are referencing an external file within
their spreadsheet. When the spreadsheet is loaded into the database
the external file is not there, and shouldn't be. When I detatch the
spreadsheet to print it out Excel recognizes that there's a link to an
external file and tries to find that file & open it. That's when I
get the "File Not Found" message and the "Open File" Dialog box
appears. The external file is categorically not necessary and
reference to it should be deleted prior to stuffing the spreadsheet
into the database. But you know users.....

Anyway here's the snippit of my code. BTW I suppress the alerts and
other links; that works fine. This code is in Powerbuilder script.
Only the salient parts are listed. Suppressing the update links and
the alerts works. I just need to either intercept the Open File
Dialog box or disable it. If there's any other way to do this I
appreciate your input.

/****************************************************
integer li_result
integer li_files_printed = 0;
long ll_pos
long ll_strlen

string ls_file_list
string ls_filename
string ls_partial_name
string ls_archive_filename
string ls_htmlstring

ls_htmlstring = '';
ls_file_list = as_files;

oleobject lo_ole_object

lo_ole_object = create oleobject
// create object
li_result = lo_ole_object.ConnectToNewObject("excel.application")
// connect to ole object; connect the ole object to excel

If li_result <> 0 Then
// Make sure connection to Excel application ok.
ls_htmlstring = "OLE ERROR: Could not connect to Excel. Error
Number was: " + string(li_result)+ is_break
ls_htmlstring += "Userid: "+is_userid+" File "+ls_filename+" not
printed.";
uof_log_error(ls_htmlstring);
destroy lo_ole_object
// destroy OLE object
Return ls_htmlstring
End If
.
.
.
.
.
// parameter passed in: number of files to print
// if there are files to print then for all the spreadsheets to print
// process file list to create full path names
// make sure spreadsheet exists (has been detached)

ls_archive_filename = PRINT_ARCHIVE_DIR + ls_partial_name;
//save archive name
ls_filename = PRINT_DIR + ls_partial_name;
//create full filename
if (FileExists(ls_filename)) then
//does file exist?

lo_ole_object.workbooks.open(ls_filename);
// open file to be printed
lo_ole_object.Application.DisplayAlerts = false;
// shut off any dialog alerts
lo_ole_object.Application.AskToUpdateLinks = false;
// shut off request to update links
lo_ole_object.ActiveWorkbook.SaveAs(ls_archive_filename);
// move file to archive directory
lo_ole_object.workbooks.close();
// close file to be printed

lo_ole_object.workbooks.open(ls_archive_filename);
// open file to be printed in archive dir
lo_ole_object.Application.DisplayAlerts = false;
// shut off any dialog alerts
lo_ole_object.Application.AskToUpdateLinks = false;
// shut off request to update links
lo_ole_object.ActiveWorkbook.Printout();
// print workbook
lo_ole_object.workbooks.close();
// close file to be printed

if FileExists(ls_archive_filename) then
FileDelete(ls_filename);
// delete original file.
end if;

end if;
.
.
.
.
lo_ole_object.application.quit()
// shut down Excel
lo_ole_object.disconnectobject()
// disconnect from OLE object
destroy lo_ole_object
// destroy OLE object
/********************************************************************/
TIA

Cathy Finnegan

[Tom Ogilvy]

Catherine,
This line can be improved to solve your problem:

lo_ole_object.workbooks.open(ls_filename);

lo_ole_object.workbooks.open(ls_filename,0);

Here is the argument list:
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMRU)

UpdateLinks Optional Variant. Specifies the way links in the file are
updated. If this argument is omitted, the user is prompted to specify how
links will be updated. Otherwise, this argument is one of the values listed
in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

Hopefully this should stop the update of links. The setting of

> lo_ole_object.Application.AskToUpdateLinks = false;

to false says: "Don't ask, update the links" so this is not what you.
Also, you are setting it after the workbook is opened. The use of the
updatelinks argument in the workbooks.open method is about the only way to
achieve you objective.

HTH,
Tom Ogilvy

Catherine Finnegan <cae...@clark.net> wrote in message
news:3897287e...@msnews.microsoft.com...

> file://save archive name

> ls_filename = PRINT_DIR + ls_partial_name;

> file://create full filename
> if (FileExists(ls_filename)) then
> file://does file exist?

[Catherine Finnegan]

Tom:

Thanks; works like a charm.

Cathy Finnegan