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.dicks-blog.com/archives/...s-to-retrieve-data-from-underlying-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, Rand