+ Reply to Thread
Results 1 to 11 of 11

Linking Multiple Workbooks

  1. 09-15-2009, 12:09 AM #1
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Linking Multiple Workbooks

    Hi all,

    I am currently looking at constructing a workbook (call it "A") that has links to several thousand other workbooks (call them "B's")that will be placed in a folder one level down in the folder tree. I want a column in "A" that looks at the same cell in each "B's" workbook for the corresponding column name in the adjacent column in "A".

    To produce the link my initial thoughts would be to use concatenation and then simply special paste the value back into the respective cells.

    It seems like a simple task to complete but, I am unsure if it is feasable so that the data in "A" refreshes without opening the workbooks "B's". Also what code to use for the link?

    Any suggestions or help would be appreciated.

    Many Thanks
    4blue
    Reply With QuoteRegister To Reply
  2. 09-15-2009, 12:29 AM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Linking Multiple Workbooks

    As long as they are direct references =[someWorkbook.xls]someSheet!someRange, you will only be testing the number of external links that Excel supports.

    You cannot use INDIRECT with closed workbooks.

    MoreFunc has a function (I'm told) INDIRECT.EXT that allows external references to close workbooks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 09-15-2009, 12:47 AM #3
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Linking Multiple Workbooks

    Thank you for that.

    What is the difference between direct and indirect references?

    Also, what would the required file name be to link to the folder containing "B's". In some computer languages it can be "..\" or does it have to be the complete file path?

    Many thanks again
    4blue
    Reply With QuoteRegister To Reply
  4. 09-15-2009, 12:54 AM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Linking Multiple Workbooks

    If you open the referenced workbook and create the reference by mouse-igating, it will show the workbook in the formula sans path, and automagically change the formula to add the path when you close the referenced workbook.

    If you create the formula by manual entry, you need to enter the fully-qualified path in the formula.

    By an indirect reference, I mean one where you put some or all of the workbook path, workbook name, sheet name, and cell reference in one or more separate cells, and then use string catenation to form the reference, wrapped with the INDIRECT function. See Help for INDIRECT.
    Last edited by shg; 09-15-2009 at 12:57 AM.
    Reply With QuoteRegister To Reply
  5. 09-15-2009, 03:51 AM #5
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Linking Multiple Workbooks

    I am currently opening workbook "B" and then completing a special paste into workbook "A". The result is "#REF!". Can workbook "B" be saved as a CSV file format?
    Reply With QuoteRegister To Reply
  6. 09-15-2009, 09:51 AM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Linking Multiple Workbooks

    A CSV file is a text file, it has no formulas or references. The text is assembled in a way that enables separation of fields via commas.
    Reply With QuoteRegister To Reply
  7. 09-15-2009, 11:52 PM #7
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Linking Multiple Workbooks

    Thank you for the help again.

    Is it possible to link to a csv file and then only use one of the comma seperated data sections to be input into a cell?

    4blue
    Last edited by 4blue; 09-16-2009 at 12:21 AM.
    Reply With QuoteRegister To Reply
  8. 09-16-2009, 12:30 AM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Linking Multiple Workbooks

    I suppose it could be done non-trivially with VBA (since csv wraps text fields containing embedded commas with quotes).

    I'm not volunteering -- it sounds like bad design.
    Reply With QuoteRegister To Reply
  9. 09-16-2009, 12:44 AM #9
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Linking Multiple Workbooks

    Would you have any suggestions on how it could work.

    I am wanting to download the data from YahooFinance Using a program called 'Yahoo & Google Historical Quotes' which basically runs a series of requests through yahoo or google to retrieve end of day data from Yahoo or google all stocks that you have requested. This data (OPEN,HIGH,LOW,ClOSE,VOLUME) is stored in CSV format with the option to append a different extension to each file. There is a seperate file for each individual stock.

    And that is where the connecting to external data comes in.

    4blue
    Reply With QuoteRegister To Reply
  10. 09-16-2009, 01:05 AM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Linking Multiple Workbooks

    I don't know how wedded you are to YahooFinance, but if you Google excel 2007 stock quotes you will find lots of alternatives designed to work in Excel.

    I don't use any of them, and so cannot offer a suggestion, other than the belief that sniffing through a lot of CSV files to get the answers is the wrong approach.
    Reply With QuoteRegister To Reply
  11. 09-16-2009, 01:58 AM #11
    4blue
    4blue is offline
    Registered User
    Join Date
    09-03-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Linking Multiple Workbooks

    Hi,

    I am not really wedded to Yahoo, I already had the means to get the data so tried to utilise that method first. Have had a little look at MSN money, unfortunately they only provide quotes to 2 decimal places, instead of 3.


    Will have a little look and see what others have been doing.

    Thank you again for all the help.
    4blue
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1