[ACCEPTED]-ExecuteExcel4Macro to get value from closed workbook-excel-4.0

Accepted answer

Score: 27

It depends on how you use it. The open file 3 dialog box is being showed to you because 2 the "strPath" doesn't have a "\" in the 1 end ;)

Try this code. It works

Tried and Tested

Option Explicit

Sub Sample()
    Dim wbPath As String, wbName As String
    Dim wsName As String, cellRef As String
    Dim Ret As String

    'wbPath = "C:\Documents and Settings\Siddharth Rout\Desktop\"
    wbPath = "C:\Users\my.name\Desktop\"

    wbName = "QOS DGL stuff.xls"
    wsName = "ACL"
    cellRef = "C3"

    Ret = "'" & wbPath & "[" & wbName & "]" & _
          wsName & "'!" & Range(cellRef).Address(True, True, -4150)

    MsgBox ExecuteExcel4Macro(Ret)
End Sub

Score: 5

Similar application, but no hard coded paths 7 as in the examples above. This function 6 copies the value from another closed workbook, similar 5 to the =INDIRECT() function, but not as 4 sophisticated. This only returns the value...not 3 a reference..so it cannot be used with further 2 functions which require references (i.e.: VLOOKUP()). Paste 1 this code into a new VBA module:

'Requires filename, sheetname as first argument and cell reference as second argument
'Usage: type in an excel cell -> =getvalue(A1,B1)
'Example of A1 -> C:\TEMP\[FILE1.XLS]SHEET1'
'Example of B1 -> B3
'This will fetch contents of cell (B3) located in (sheet1) of (c:\temp\file1.xls)

'Create a module and paste the code into the module (e.g. Module1, Module2)

Public xlapp As Object

Public Function getvalue(ByVal filename As String, ref As String) As Variant

' Retrieves a value from a closed workbook
    Dim arg As String
    Dim path As String
    Dim file As String

    filename = Trim(filename)

    path = Mid(filename, 1, InStrRev(filename, "\"))
    file = Mid(filename, InStr(1, filename, "[") + 1, InStr(1, filename, "]") - InStr(1, filename, "[") - 1)

    If Dir(path & file) = "" Then
        getvalue = "File Not Found"
        Exit Function
    End If

    If xlapp Is Nothing Then
        'Object must be created only once and not at each function call
        Set xlapp = CreateObject("Excel.application")
    End If


    ' Create the argument
    arg = "'" & filename & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

    'Execute an XLM macro
    getvalue = xlapp.ExecuteExcel4Macro(arg)

End Function

Source: stackoverflow.com

[ACCEPTED]-ExecuteExcel4Macro to get value from closed workbook-excel-4.0

Excel issue "excel 4.0 function stored in defined names

What does "=EMBED("Forms.CheckBox.1","")" do for checkboxes?

Write a formula in an Excel Cell using VBA

VBA editor auto-deletes spaces at the ends of lines

Forcing Garbage Collection

Convert image (jpg) to base64 in Excel VBA?

Hiding a sheet in Excel 2007 (with a password) OR hide VBA code in Excel

VBA Cell address of Max()

How to push value into non pre specific array size?

Read XML Attribute VBA