How to avoid this Alert screen

[jesmin]

Hi Friends:
I have a workbook(say "myprog.xls") with 4 sheets--val1,rpt1,val2,rpt2.
Manually(not in VB module)I copied data from val1 and paste them with Link in rpt1. So data in rpt1 has reference to val1. Similarly, in rpt2 I copied and link data from val2.
Now at the end I did SaveAs with the 2 sheets--rpt1 and rpt2 and created a new workbook named "Totalrpt" which has only 2 sheets--rpt1,rpt2.
Now each time, I am running my main program "myprog.xls" its perfectly creating my new workbook "Totalrpt". But when I am going to OPEN this Totalrpt manually(say by double clicking) its opening an alert screen. This alert screen asking to Update or not to Update the excel file.
and saying that this file has reference to another workbook.

I dont want to see this alert screen. Each time I will open "Totalrpt" it will automatically have the latest data and will not prompt me to Update or not. This is for the user and user will only open the latest data without any vb code and without any alert screen.

How can I do it in VB.

My SaveAs code:
set ns = Thisworkbook.range(array("rpt1","rpt2")
ns.copy
with ns
.parent.SaveAs filename "Totalrpt.xls" Fileformat:=xlnormal
end with
Thanks anyone

[Dave Peterson]

You could have a formula that on one of those two sheets (rpt1 or rpt2) that
refers back to the val1 or val2 worksheets.

Or you could even have a named range that refers back to that other workbook's
worksheets.

When I can't find links, I'll use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And if I want to look at names, I'll use Jan Karel Pieterse's (with Charles
Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp



jesmin wrote:
>
> Hi Friends:
> I have a workbook(say "myprog.xls") with 4 sheets--val1,rpt1,val2,rpt2.
>
> Manually(not in VB module)I copied data from val1 and paste them with
> Link in rpt1. So data in rpt1 has reference to val1. Similarly, in rpt2
> I copied and link data from val2.
> Now at the end I did SaveAs with the 2 sheets--rpt1 and rpt2 and
> created a new workbook named "Totalrpt" which has only 2
> sheets--rpt1,rpt2.
> Now each time, I am running my main program "myprog.xls" its perfectly
> creating my new workbook "Totalrpt". But when I am going to OPEN this
> Totalrpt manually(say by double clicking) its opening an alert screen.
> This alert screen asking to Update or not to Update the excel file.
> and saying that this file has reference to another workbook.
>
> I dont want to see this alert screen. Each time I will open "Totalrpt"
> it will automatically have the latest data and will not prompt me to
> Update or not. This is for the user and user will only open the latest
> data without any vb code and without any alert screen.
>
> How can I do it in VB.
>
> My SaveAs code:
> set ns = Thisworkbook.range(array("rpt1","rpt2")
> ns.copy
> with ns
> parent.SaveAs filename "Totalrpt.xls" Fileformat:=xlnormal
> end with
> Thanks anyone
>
> --
> 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

[jesmin]

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

[Dave Peterson]

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

[jesmin]

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.

[Dave Peterson]

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

[jesmin]

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

[Dave Peterson]

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