I have produced a procedure that opens up a external worksheet, searchs
for a value and then returns to original sheet and pastes, this is in a
loop and continues until it runs out of things to find.

My problem is i would only like to open the excel file once and see the
message box that asks whether you want to update links once. At present
it is asking me on every loop.

I have been using this within my loop
Set bk2 = Workbooks.Open(filepath2)

Ive also tried to use this statement before loop starts and then try
bk2.activate
but this still brings up the message box and asks to update links.

How can i either suppress this message or just update file at the start
of the loop and avoid being asked again? thanks


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=531884

Note that this code does not select bk2 - there usually is not reason to.

Dim bk1 as Workbook, bk2 as Workbook
Dim cell as Range, rng as Range
Dim rng1 as Range
Set bk1 = Activeworkbook
Set bk2 = Workbooks.Open(filepath2, UpdateLinks:=1)
for each cell in Bk1.worksheets(1).Range("A1:A10")
set rng = bk2.Worksheets(1).Cells
set rng1 = rng.Find(cell.Value)
if not rng1 is nothing then
cell.offset(0,1).value = rng1.offset(0,2).value
end if
Next
bk2.close SaveChanges:=False


--
Regards,
Tom Ogilvy


"cereldine" wrote:


I have produced a procedure that opens up a external worksheet, searchs
for a value and then returns to original sheet and pastes, this is in a
loop and continues until it runs out of things to find.

My problem is i would only like to open the excel file once and see the
message box that asks whether you want to update links once. At present
it is asking me on every loop.

I have been using this within my loop
Set bk2 = Workbooks.Open(filepath2)

Ive also tried to use this statement before loop starts and then try
bk2.activate
but this still brings up the message box and asks to update links.

How can i either suppress this message or just update file at the start
of the loop and avoid being asked again? thanks


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=531884