+ Reply to Thread
Results 1 to 6 of 6

Dynamic Path Names For External Links

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Guarapari, Brazil
    MS-Off Ver
    Excel 2010
    Posts
    2

    Smile Dynamic Path Names For External Links

    I come here to ask for some help from my VBA expert's friends.

    I have about 150 .xls files where I have the measurements of the buildings where I've worked in and I need the data of these 150 workbooks in a single main file where I have monthly measurements with a general report and charts.

    This main workbook has fields with pathnames which I created using concatenate, generating an auxiliary spreadsheet that has external reference to each secondary's file.

    It has about 70 sheets in the file, from which 50 sheets are summary of monthly measurements; And what I want is automate the data collection through the closed workbooks without using indirect function neither open all woorkbooks to update the main file.

    I researched a lot about "dynamic external references" and "pathnames for Dynamic External Links" and similar issues ... but I couldnt go ahead cause my VBA's knowledge is s*cks yet.

    So... the idea is create a macro which uses formulas and autofill function in VBA to copy concatenated values, similar to excel's native external reference, to open and update data from all my 150 closed workbooks to this 50 sheets in the main workbook, without need to use the INDIRECT function and without need to create macros that open all 150 workbooks. Here is a link to "Rylo" and "Freud1"'s insight
    http://www.excelforum.com/excel-gene...nal-links.html

    I ll try post my cleaned main woorkbook.

    Sorry about my poor english language skills.
    and Thanks all.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Dynamic Path Names For External Links

    If possible try to use Indirect.Ext function of Morefunc Addin which you can get by doing a google search.

    INDIRECT.EXT
    Returns the value of a cell or range specified by its address (text string).
    Unlike the INDIRECT built-in function, INDIRECT.EXT can also return the value of a cell whose workbook is closed.

    SYNTAX :
    =INDIRECT.EXT(Reference,Volatile,A1-Style)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Dynamic Path Names For External Links

    Hi, sixthsense, Can you please provide an example?
    I am not able to use the folrmula provided by you. May be I am doing something wrong

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Dynamic Path Names For External Links

    Have you seen this text?

    If possible try to use Indirect.Ext function of Morefunc Addin which you can get by doing a google search.

  5. #5
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Dynamic Path Names For External Links

    Oopss.... Sorry......

    Thanks for quick reply

  6. #6
    Registered User
    Join Date
    06-11-2013
    Location
    Guarapari, Brazil
    MS-Off Ver
    Excel 2010
    Posts
    2

    Cool Re: Dynamic Path Names For External Links

    Quote Originally Posted by :) Sixthsense :) View Post
    If possible try to use Indirect.Ext function of Morefunc Addin which you can get by doing a google search.

    INDIRECT.EXT
    Returns the value of a cell or range specified by its address (text string).
    Unlike the INDIRECT built-in function, INDIRECT.EXT can also return the value of a cell whose workbook is closed.

    SYNTAX :
    =INDIRECT.EXT(Reference,Volatile,A1-Style)
    Hi all!

    Sorry for the delay to reply.

    Sixthsense,

    I appreciate your attention, but I wouldn't like to use the "INDERCT.EXT" and "Morefunc Addin", because I'll must install the addin on every PC to open the spreadsheet. The best solution that I've founded is the "Rylo" in this link > http://www.excelforum.com/excel-gene...nal-links.html, but I have to apply in my spreadsheet yet. I've attached it in this post as: "Example Measurements Workbook".

    So... I had a little bit difficult to modify the VBA code cells range from "Rylo" Solution to my spreadsheet...

    Could u or anybody handle me with this code?

    The best way is use the path field as constant in all sheets... and when I need move the "Main Workbook", it will just change a single path file base (Main Secundary) to update all the woorkbook.
    Below i've attached the zipped files and the code that I want to use.

    Please Login or Register  to view this content.
    Example Measurements Workbook by LeoAlves.zip
    Dynamic Pathname for External Links Auto Updated by RYLO Macro.zip
    Last edited by LeoAlves; 07-01-2013 at 12:19 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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