Hello,

I have written a VB6 application that parses apart the sheets in an Excel
workbook into a number of small workbooks based on values in a column.

The spreadsheets have formulas, some of them reference other workbooks, so
whenever the program opens a sheet I get a dialog asking if I want to
update. I can get rid of this by manually replacing all the formulas with
data before running the program, but the workbook has five sheets and a
number of hidden columns, so it is a bit of work to do it. The client would
prefer if this was handled automatically.

I think I know how to replace all the formulas with data by using code, but
I can't figure out how to get around the dialog the first time I open a
sheet.

The simplest would be to have the program respond to the dialog by selecting
'No' when the program is running.

An alternate solution would be to replace all the formulas with data before
starting to process the workbook, which would require the user to respond
only once, unless I could find a way to respond automatically to the dialog.

Any help with this would be appreciated.

Ragnar

Will you be running this application only on your own pc?

If yes, you can toggle the setting (it's a user by user setting) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get updated.

This setting is for the individual user--and affects all their workbooks--so it
might not be useful if you have multiple users.

I don't speak the VB6, but if I were doing this via VBA, I'd create a dummy
workbook whose only purpose is to open the original workbook with links updated
(or not):

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook which opens your real workbook (with the links
set the way you want).

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"

Ragnar Midtskogen wrote:
>
> Hello,
>
> I have written a VB6 application that parses apart the sheets in an Excel
> workbook into a number of small workbooks based on values in a column.
>
> The spreadsheets have formulas, some of them reference other workbooks, so
> whenever the program opens a sheet I get a dialog asking if I want to
> update. I can get rid of this by manually replacing all the formulas with
> data before running the program, but the workbook has five sheets and a
> number of hidden columns, so it is a bit of work to do it. The client would
> prefer if this was handled automatically.
>
> I think I know how to replace all the formulas with data by using code, but
> I can't figure out how to get around the dialog the first time I open a
> sheet.
>
> The simplest would be to have the program respond to the dialog by selecting
> 'No' when the program is running.
>
> An alternate solution would be to replace all the formulas with data before
> starting to process the workbook, which would require the user to respond
> only once, unless I could find a way to respond automatically to the dialog.
>
> Any help with this would be appreciated.
>
> Ragnar

--

Dave Peterson
(E-Mail Removed)

Thank you Dave,

That was easy, I am opening the workbook with VB code using the
Workbooks.Open method (VBA is almost identical to VB6), but I did not notice
the UpdateLinks argument.

Ragnar