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

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

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

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.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=515142

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


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=515142

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