Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Files (Office 97)

    I wrote a macro that opens a file then copies one of its sheets into a template file. Everything works well except whenever I open the "combined" file, I get the message "The workbook you opened contains automatic links to information in another workbook. Do you wnat to update".
    How can I stop this message from appearing or if I can't, how can I determine what the links are so I can remove?
    Thanks. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Linked Files (Office 97)

    It would help to see the code. Here's some belt-and-suspenders code I use:

    On Error Resume Next
    Application.AskToUpdateLinks = False
    Err.Clear
    Application.DisplayAlerts = False
    On Error Resume Next
    Set wbWkBk = Workbooks.Open(Filename:=strFileName, UpdateLinks:=False)
    Err.Clear
    Application.DisplayAlerts = True
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Files (Office 97)

    It depends on what you really want.

    It sounds like the way you are doing the copy the formulas in the new workbook are changed to links to the cells in the original rather than being links to the corresponding cells in the new workbook. Is that what you want to happen? You are probably going to have to show us the code and possibly the workbook that the sheet is being copied from to get a good answer on this one.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Files (Office 97)

    You are probably right. I just think it would be way too complicated and maybe I'm asking the wrong question. Let me try to explain further. I use a template where I save many files under different names. I changed the template and in an effort to have all previously saved files reflect the changes, I created a macro that copied the old file into the template. Both the old and the new use many macros with the same names. I'm sure this is what is causing the problem. I think if I could close the old file after copying one of its sheets to the template, I could save the template under the old file name and have only one "surviving" file. I can't figure out how to get back to the old file without naming it. I must be able to do this because after I "combine" files, I have two in the widow.

    Here is a copy of a portion of the macro. You can see I copy the opened file to the template. How can I get back to the opened file (to close it ) without naming it?
    Sub openfile2()
    unprotect1
    Dim Filer As String
    Filer = Application.GetOpenFilename
    Workbooks.Open FileName:=Filer
    Sheets("Spreadsheet").Select
    ActiveSheet.Unprotect
    unhide
    Sheets("Cash flow").Select
    ActiveSheet.Unprotect
    Range("E53:Y53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Spreadsheet").Select
    Range("E135:Y135").Select
    ActiveSheet.Paste
    Sheets("Top Sheet").Visible = True
    Sheets("Top Sheet").Select
    ActiveSheet.Unprotect
    Range("e82:m98").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Spreadsheet").Select
    Range("e136:m152").Select
    ActiveSheet.Paste
    Sheets("Top sheet").Select
    Range("e38:m49").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Spreadsheet").Select
    Range("e157:m168").Select
    ActiveSheet.Paste
    Range("a10").Select
    ActiveSheet.Copy before:=Workbooks("GRAPH3ctest41").Sheets("Spreads heet")
    ActiveWindow.WindowState = xlMaximized
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "You are about to convert the data from your old file to the latest formatted spreadsheet. After convertion, check the topsheet to be sure that the PAYS PROMPT TO, & the 1 IF BANK OR FACTOR SECURED cells are properly filled in. To Continue, Click Yes. To stop, and return to the main menu, click no, then click O.K." ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Begin Conversion Macro" ' Define title.
    Help = "DEMO.HLP" ' Define Help file.
    Ctxt = 1000 ' Define topic
    ' context.
    ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then loadoldall ' User chose Yes.
    MyString = "Yes" ' Perform some action.
    If Response = vbNo Then deletesheet
    gotomainmenu
    ' User chose No.
    MyString = "No" ' Perform some action.

    ' User chose No.

    End Sub

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Files (Office 97)

    I have tried your code, and the only way that I can get the symptoms you describe is if the cells that are being copied contain links to cells in other sheets in the workbook. For example, if cell E53 on sheet "Cash flow" contains a formula like =Sheet2!A1, then after the sheet is copied to the workbook "GRAPH3ctest41" there is a link back to the original workbook Sheet2!A1. This may be what is happening to you, but I can't be sure without seeing the complete macro and the workbook that is being copied.

    BTW, that code looks like it was created by using the macro recorder. It could be made much more efficient by removing all those selects.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Files (Office 97)

    If you copy all sheets of the "original files" in one go into the template, you should be OK.

    Something like this:

    Sub test()
    ActiveWorkbook.Sheets.Copy Before:=Workbooks("Book3").Sheets(1)
    End Sub

    ANother suggestion:

    Why not put your code in a separate workbook as opposed to in your template? That way, updating many files is not needed when you change code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Files (Office 97)

    Unfortunetly, there are several combined macros that I used. Unless I attach the entire workbook, there is no way for me to get you what you need.
    If there is a way to do this, I will. Just tell me how it is done.

    Thanks for your help. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

Posting Permissions

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