Get data from a cell in a closed workbook

March 17, 2008

There are lots of ways to get data from other workbooks. This has helped when grabbing summary data for report summaries (see here for creating an email)

Example:

p = “\some\directory”
f = “Some fake file.xls”
s = “Sheet1″
a = “A1″
result = GetValue(p, f, s, a)

Private Function GetValue(path, file, sheet, ref)
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ Source: http://www.j-walk.com/ss/excel/tips/tip82.htm
‘ 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) = “” 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

Posted in Excel