+ Reply to Thread
Results 1 to 13 of 13

Can indirect() work using closed external files??

  1. #1
    Hall
    Guest

    Can indirect() work using closed external files??

    Workbook A has a cell that gets data from workbook B as an external link.
    It does this using the indirect function because it needs to concatenate the
    path and filename from other cells.

    Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

    So can indirect work using closed external files or must the external files
    be opened?

    Is there a clever way of making that work?

    Thanks!



  2. #2
    azidrane
    Guest

    Re: Can indirect() work using closed external files??

    I know they must be open and I have not found a way to make them work
    yet. Anyone?


  3. #3
    Harlan Grove
    Guest

    Re: Can indirect() work using closed external files??

    Hall wrote...
    >Workbook A has a cell that gets data from workbook B as an external link.
    >It does this using the indirect function because it needs to concatenate the
    >path and filename from other cells.
    >
    >Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
    >
    >So can indirect work using closed external files or must the external files
    >be opened?
    >
    >Is there a clever way of making that work?


    There is *NO* way to make INDIRECT read from closed workbooks. The
    alternatives are given in the following archived posting.

    http://groups.google.com/group/micro...443753560f0075

    (or http://makeashorterlink.com/?B34B15DCC ).


  4. #4
    Dave Peterson
    Guest

    Re: Can indirect() work using closed external files??

    Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
    workbook.

    You can find the function at Harlan's FTP site:
    ftp://members.aol.com/hrlngrv/
    Look for pull.zip

    Hall wrote:
    >
    > Workbook A has a cell that gets data from workbook B as an external link.
    > It does this using the indirect function because it needs to concatenate the
    > path and filename from other cells.
    >
    > Problem is that if workbook B is closed, the cell in workbook A shows #REF!.
    >
    > So can indirect work using closed external files or must the external files
    > be opened?
    >
    > Is there a clever way of making that work?
    >
    > Thanks!


    --

    Dave Peterson

  5. #5
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Quote Originally Posted by Hall
    Workbook A has a cell that gets data from workbook B as an external link.
    It does this using the indirect function because it needs to concatenate the
    path and filename from other cells.

    Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

    So can indirect work using closed external files or must the external files
    be opened?

    Is there a clever way of making that work?

    Thanks!


    Let me know if you still need help because i may have a solution.

  6. #6
    Hall
    Guest

    Re: Can indirect() work using closed external files??

    Well sure, I'd love to take you up on your offer since I have no idea how to
    use vba or the solutions I received so far.

    "vane0326" <vane0326.24scrn_1142551501.2859@excelforum-nospam.com> wrote in
    message news:vane0326.24scrn_1142551501.2859@excelforum-nospam.com...
    >
    > Hall Wrote:
    >> Workbook A has a cell that gets data from workbook B as an external
    >> link.
    >> It does this using the indirect function because it needs to
    >> concatenate the
    >> path and filename from other cells.
    >>
    >> Problem is that if workbook B is closed, the cell in workbook A shows
    >> #REF!.
    >>
    >> So can indirect work using closed external files or must the external
    >> files
    >> be opened?
    >>
    >> Is there a clever way of making that work?
    >>
    >> Thanks!

    >
    >
    >
    > Let me know if you still need help because i may have a solution.
    >
    >
    > --
    > vane0326
    > ------------------------------------------------------------------------
    > vane0326's Profile:
    > http://www.excelforum.com/member.php...o&userid=14731
    > View this thread: http://www.excelforum.com/showthread...hreadid=522861
    >




  7. #7
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Here is a link I recently posted.


    http://www.excelforum.com/showthread...33#post1522233


    post back if you have any questions.

  8. #8
    Hall
    Guest

    Re: Can indirect() work using closed external files??

    I have no idea how this is supposed to make an indirect work on closed
    files.

    "vane0326" <vane0326.24sn6m_1142565000.3568@excelforum-nospam.com> wrote in
    message news:vane0326.24sn6m_1142565000.3568@excelforum-nospam.com...
    >
    > Here is a link I recently posted.
    >
    >
    > http://www.excelforum.com/showthread...33#post1522233
    >
    >
    > post back if you have any questions.
    >
    >
    > --
    > vane0326
    > ------------------------------------------------------------------------
    > vane0326's Profile:
    > http://www.excelforum.com/member.php...o&userid=14731
    > View this thread: http://www.excelforum.com/showthread...hreadid=522861
    >




  9. #9
    Peo Sjoblom
    Guest

    Re: Can indirect() work using closed external files??

    I think it is easier if you download and install Harlan's PULL function,
    here's how to install macros or UDFs

    http://www.mvps.org/dmcritchie/excel/install.htm

    if you look at the links you were given or if you search Google for the PULL
    function there are examples, another way would be to install Morefunc from
    here

    http://xcell05.free.fr/

    it has a function called INDIRECT.EXT

    --

    Regards,

    Peo Sjoblom

    Northwest Excel Solutions

    www.nwexcelsolutions.com

    (remove ^^ from email address)

    Portland, Oregon




    "Hall" <hall@garp.org> wrote in message
    news:e5ad6NXSGHA.1948@TK2MSFTNGP09.phx.gbl...
    >I have no idea how this is supposed to make an indirect work on closed
    >files.
    >
    > "vane0326" <vane0326.24sn6m_1142565000.3568@excelforum-nospam.com> wrote
    > in message news:vane0326.24sn6m_1142565000.3568@excelforum-nospam.com...
    >>
    >> Here is a link I recently posted.
    >>
    >>
    >> http://www.excelforum.com/showthread...33#post1522233
    >>
    >>
    >> post back if you have any questions.
    >>
    >>
    >> --
    >> vane0326
    >> ------------------------------------------------------------------------
    >> vane0326's Profile:
    >> http://www.excelforum.com/member.php...o&userid=14731
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=522861
    >>

    >
    >



  10. #10
    Harlan Grove
    Guest

    Re: Can indirect() work using closed external files??

    Hall wrote...
    >Well sure, I'd love to take you up on your offer since I have no idea how to
    >use vba or the solutions I received so far.


    To repeat, INDIRECT will *NEVER* work on closed files. vane...'s
    approach is a kludge which opens referenced workbooks then closes them
    after recalc. It uses VBA. My own pull udf uses VBA, Microsoft's
    SQL.REQUEST udf uses the old XLM macro language. You're likely best off
    downloading and installing Laurent Longre's MOREFUNC.XLL add-in, which
    is freely available at http://xcell05.free.fr/english/. Follow its
    instructions for installing it. Once you have done so, you can use it's
    INDIRECT.EXT function which can reference ranges and some defined names
    in closed workbooks.


  11. #11
    Hall
    Guest

    Re: Can indirect() work using closed external files??

    Thanks Harlan. That helps put this in context and gives me direction to
    try.

    "Harlan Grove" <hrlngrv@aol.com> wrote in message
    news:1142569846.053385.20670@u72g2000cwu.googlegroups.com...
    > Hall wrote...
    >>Well sure, I'd love to take you up on your offer since I have no idea how
    >>to
    >>use vba or the solutions I received so far.

    >
    > To repeat, INDIRECT will *NEVER* work on closed files. vane...'s
    > approach is a kludge which opens referenced workbooks then closes them
    > after recalc. It uses VBA. My own pull udf uses VBA, Microsoft's
    > SQL.REQUEST udf uses the old XLM macro language. You're likely best off
    > downloading and installing Laurent Longre's MOREFUNC.XLL add-in, which
    > is freely available at http://xcell05.free.fr/english/. Follow its
    > instructions for installing it. Once you have done so, you can use it's
    > INDIRECT.EXT function which can reference ranges and some defined names
    > in closed workbooks.
    >




  12. #12
    boisgontier@hotmail.com
    Guest

    Re: Can indirect() work using closed external files??


    http://cjoint.com/?drvBQnGZDT

    Sub LitClasseurFerm=E9()
    ChampOuCopier =3D "C2:C3"
    Chemin =3D ActiveWorkbook.Path & "\source"
    Fichier =3D "stock.xls"
    onglet =3D "Janvier"
    ChampAlire =3D "B2:B3"
    LitChamp ChampOuCopier, Chemin, Fichier, onglet, ChampAlire
    End Sub

    Sub LitChamp(ChampOuCopier, Chemin, Fichier, onglet, ChampAlire)
    Range(ChampOuCopier).Formula =3D "=3D'" & Chemin & "\[" & Fichier & "]"
    & onglet & "'!" & ChampAlire
    Range(ChampOuCopier).Value =3D Range(ChampOuCopier).Value
    End Sub

    JB


  13. #13
    Registered User
    Join Date
    12-28-2015
    Location
    Israel
    MS-Off Ver
    10
    Posts
    1

    Re: Can indirect() work using closed external files??

    Try to use INDEX function
    You can define name for the table in the other file

+ 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