1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Excel Worksheet Cell info to be used in Macro

Discussion in 'Business Applications' started by SulgraveRoad, Sep 7, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. SulgraveRoad

    SulgraveRoad Thread Starter

    Joined:
    Sep 7, 2008
    Messages:
    5
    Microsoft Excel MS Excel 2003

    I would like to pull information from worksheet cells (specifically file path and name) to use in an auto open macro in order to provide a workaround on the limitation of the INDIRECT function's inability to access data in closed worksheets.

    How do I create a reference in the Visual Basic macro to pul the data from the worksheet?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    Do you mean you have the path in a specific cell, then you could refer to it like I have below... the y variable is a named reference on the sheet.



    Public Sub test()

    Dim x As String
    Dim y As String

    x = Range("A1")
    y = Range("RNGb1")


    MsgBox x
    MsgBox y

    End Sub
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Laurent Longre's MOREFUNC add-in includes a function (INDIRECT.EXT) to address the closed workbook limitation. Try it & see if it works for you, if not post back with more info.

    http://xcell05.free.fr/morefunc/english/
     
  4. SulgraveRoad

    SulgraveRoad Thread Starter

    Joined:
    Sep 7, 2008
    Messages:
    5
    Ziggy, thank you. I'm getting closer, but still need help.

    Here's what I have to open the desired file:
    Workbooks.Open Filename:= _
    "C:\Documents and Settings\thoward\Desktop\Client\CKP\E&P\CKP E&P Summary.xls"
    Now with your assistance, I inserted the following:
    Dim x As String
    Dim y As String
    x = Range("D1")
    y = Range("J1")
    Where the contents of cell
    D1 is: "C:\Documents and Settings\thoward\Desktop\Client\CKP\E&P\
    and
    J1 is: CKP E&P Summary.xls"

    Can you tell me how I use x and y in the Workbooks.Open formula?
    Workbooks.Open Filename:= _
    xy
    This didn't work, neither did a bunch of other tries/combinations.

    Can you tell me what I'm missing?

    TY
     
  5. SulgraveRoad

    SulgraveRoad Thread Starter

    Joined:
    Sep 7, 2008
    Messages:
    5
    Bomb, thank you. I had read elsewhere is that the indirect.txt could be slow. I also read elsewhere that someone thought you could nest the indirect formula in a hyperlink formula, I don't think that works. Do you agree?
     
  6. SulgraveRoad

    SulgraveRoad Thread Starter

    Joined:
    Sep 7, 2008
    Messages:
    5
  7. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    USE...

    Workbooks.Open X & Y
     
  8. SulgraveRoad

    SulgraveRoad Thread Starter

    Joined:
    Sep 7, 2008
    Messages:
    5
    Thanks Ziggy!
     
  9. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    good stuff ! :) ...you can mark this thread solved in Thread tools (top right )

    bomb21...I tried your link also and it takes you to the page, but the indirect.txt link does not work
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/747831

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice