Results 1 to 3 of 3

Thread: Linking Files

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Linking Files

    When you have 3 (or more) workbooks linked in "serial" fashion, the links don't carry through as expected.

    WorkBook1 A1 = 15
    Workbook2 A1 = WorkBook1!A1 = 15
    WorkBook3 A1 = WorkBook2!A1 = 15

    Change Workbook1 A1 to 25 and save.
    Open Workbook3, Update Links, and it stays at 15.
    Open Workbook2, update links, and save, then open Workbook3 and it changes to 25.

    This is causing problems for my accountants. I supply workbook1 from SQL server. What they do with it from there is their problem. For some reason, workbook 3 is now MY problem too. I told them to link 3 directly to 1, rather than use 2 as an "interim" step, but they didn't buy that. Then I said "Fine, open 2 every day and save it". That didn't go over well, either.
    I could write a script to open 2 and save it every night, but there are actually 24 workbook2's. I can see problems happening with that more often than not.

    Does anybody have any ideas on another method for doing this?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by RedNeckGeek
    I told them to link 3 directly to 1, rather than use 2 as an "interim" step, but they didn't buy that.
    Are they stupid or something?

    I could write a script to open 2 and save it every night, but there are actually 24 workbook2's. I can see problems happening with that more often than not.

    Does anybody have any ideas on another method for doing this?
    Well, you could do an autorun script in workbook3 that quietly opens workbook2 and updates it. Simple optimization: It should only fire if the last modified date of workbook2 is older than workbook1.

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    For some reason, workbook 3 is now MY problem too. I told them to link 3 directly to 1, rather than use 2 as an "interim" step, but they didn't buy that. Then I said "Fine, open 2 every day and save it". That didn't go over well, either.
    Links mostly result in bloted workbooks and they often cause more problems. I think you will be better to access the workbook1 with a macro to retrieve the data to workbook3 directly. You could open the workbook retrieve the data, or use an ADO query. Or an Update on open MS Query to bring in the data to a data sheet in workbook3.
    ~

    Bill

Posting Permissions

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