How to avoid this Alert screen

Saved from a similar question:

You can toggle the setting (user by user, though) 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.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

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 and the links will be refreshed.
(read about that UpdateLinks argument in VBA's help.)

jesmin wrote:
>
> Hi Dave Or anyone:
>
> I am creating a report workbook file from my original workbook. The
> report sheets have link to sheets in original workbook. So when I am
> opening the report workbook, it is opening the Alert screen to update
> or not. I DO NOt want to see this alert screen. I want to add some VB
> code in my original progrm that will only grab the data from the data
> sheets without reference.
>
> Thanks
>
> --
> jesmin
> ------------------------------------------------------------------------
> jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
> View this thread: http://www.excelforum.com/showthread...hreadid=515142

--

Dave Peterson

Hi Dave:
Thanks again. I am running my main program "myprog.xls" and its code. It creates the report file "Totalrpt.xls". I dont need to update any thing. As each month I will get new data and I will run my main program. Each month I will replace the old report. This report has links to "myprog.xls".
I will only send this "Totalrpt.xls" to my client. But each time he is running this report file "Totalrpt.xls" he is getting the pop-up alert screen.
Can I write some code while I am saving my main program as "Totalrpt" so that when some one opens the "Totalrpt.xls", tehre will be no alert screen. I want to do it in VB.

You have to find out where it that link is.

If it's in a cell in a worksheet, you could just break the link by copying that
cell and pasting values.

If it's in a name, you could remove the name--if it's not used for anything
else.

I'd use those two suggestions to find the link and see what the next step is.

If it turns out that the link is in a cell in a worksheet and you can lose all
the formulas, you could add:

with worksheets("rpt1").usedrange
.copy
.pastespecial paste:=xlpastevalues
end with

But if you need some formulas, then this isn't a good suggestion.

It depends on what you want and where they are.


jesmin wrote:
>
> Hi Dave:
> Thanks again. I am running my main program "myprog.xls" and its code.
> It creates the report file "Totalrpt.xls". I dont need to update any
> thing. As each month I will get new data and I will run my main
> program. Each month I will replace the old report. This report has
> links to "myprog.xls".
> I will only send this "Totalrpt.xls" to my client. But each time he is
> running this report file "Totalrpt.xls" he is getting the pop-up alert
> screen.
> Can I write some code while I am saving my main program as "Totalrpt"
> so that when some one opens the "Totalrpt.xls", tehre will be no alert
> screen. I want to do it in VB.
>
> --
> jesmin
> ------------------------------------------------------------------------
> jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
> View this thread: http://www.excelforum.com/showthread...hreadid=515142

--

Dave Peterson

Hi Dave:
Let me simplify the problem. Say in workbook1 sheet1 I have a value in cell ,say,cells(2,5) =2000. Now in workbook2 in sheet1, my cells(3,5)=workbook1!sheet1!$e$2.
When I am opening workbook2, its opening the alert screen saying that this workbook2 has links to another workbook. Do you want to update or Not. I just dont want to show this alert screen. I want to open this workbook2 simply without any option/alert. I dont have any VB coding in workbook2. I have my VB in workbook1.
Thanks

You have to find those links and do what you want.

You can convert them to values and then send the workbook.

jesmin wrote:
>
> Hi Dave:
> Let me simplify the problem. Say in workbook1 sheet1 I have a value in
> cell ,say,cells(2,5) =2000. Now in workbook2 in sheet1, my
> cells(3,5)=workbook1!sheet1!$e$2.
> When I am opening workbook2, its opening the alert screen saying that
> this workbook2 has links to another workbook. Do you want to update or
> Not. I just dont want to show this alert screen. I want to open this
> workbook2 simply without any option/alert. I dont have any VB coding in
> workbook2. I have my VB in workbook1.
> Thanks
>
> --
> jesmin
> ------------------------------------------------------------------------
> jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
> View this thread: http://www.excelforum.com/showthread...hreadid=515142

--

Dave Peterson