Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Assistance (03)

    I have a custom function which was created years ago and I was wondering if there was an equivalent in Excel 03 as the code is referencing "ExecuteExcel4Macro". Here is the function:

    Private Function GetValue(path, file, sheet, ref)
    ' Retrieves a value from a closed workbook
    Dim arg As String

    ' Make sure the file exists
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file, vbHidden) = "" Then
    GetValue = "File Not Found"
    Exit Function
    End If

    ' Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
    Range(ref).Range("A1").Address(, , xlR1C1)

    ' Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    End Function


    Thanks for taking a look at it.
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Assistance (03)

    Nope, Excel VBA still doesn't support retrieving data from closed workbooks. Workarounds are:

    - The code you describe (from John Walkenbach: Excel Developer Tip: A VBA Function to Get a Value From a Closed File)
    - If the data in the workbook are in a database-like table format, you can use ADO to read the data.
    - Open the workbook, read the value you need, then close it again.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Assistance (03)

    Thank you Hans,

    John <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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