GetValue to extract data from closed files

[John James]

For a wide variety of purposes, I want to be able to extract data from specified locations of closed files. Direct cell links work on closed files, but this seems too inflexible when there are subsequent changes in requirements. Sadly, the Indirect formula, which allows flexibility, works only on open worksheets. (It would be of significant benefit if Microsoft made Indirect work on closed files, but that's another story)

John Walkenbach refers to an XLM macro which extracts data from a closed file:

http://www.j-walk.com/ss/excel/tips/tip82.htm

Unfortunately this macro cannot be used in a worksheet formula,

I'm looking for help flexibly applying this GetValue macro to extract data from multiple hardcoded source and target locations (different source files). Essentially I'd like a sub which could be adapted on the fly to simply add new source and target locations. Could someone please help by providing code which shows how to place source1 and source2 values (different locations in files) into target1 and target2 locations in a single file by running a macro. I'd like the macro to be simply adaptable for adding new source3 and target3 code, etc.

[If the source and target locations could be specified in spreadsheet cells then that would be the ideal but this seems impossible for closed files.]

Many thanks,

[Randy Harmelink]

Take a look at this blog entry:

http://www.*****-blog.com/archives/2...ing-workbooks/

I was able to create a function to return a single cell's value using
information from that article. For example:

Private Function GetXLSData(sFile As String, sSheet As String, sCell As
String)
Set oDB = CreateObject("ADODB.Recordset")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
sSQL = "SELECT * From [" & sSheet & "$" & sCell & ":" & sCell & "]"
oDB.Open sSQL, sConn, 3, 3, 1
GetXLSData = oDB.Fields.Item(0).Value
oDB.Close
Set oDB = Nothing
End Function

Then, my formula was:

=GetXLSData("Lists.xls", "Sheet1","B2")

[John James]

Superb, Randy