+ Reply to Thread
Results 1 to 2 of 2

open without link update

  1. #1
    Registered User
    Join Date
    03-02-2006
    Posts
    54

    open without link update

    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

  2. #2
    Tom Ogilvy
    Guest

    RE: open without link update

    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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1