Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetValue to extract data from closed files
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, -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531126 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetValue to extract data from closed files
Take a look at this blog entry:
http://www.dicks-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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetValue to extract data from closed files
Superb, Rand -- John Jame ----------------------------------------------------------------------- John James's Profile: http://www.excelforum.com/member.php...fo&userid=3269 View this thread: http://www.excelforum.com/showthread.php?threadid=53112 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GetValue - Closed Workbook? | Excel Discussion (Misc queries) | |||
Getvalue from closed workbook | Excel Discussion (Misc queries) | |||
Update DDE data from closed files | Excel Discussion (Misc queries) | |||
Extract Data from Multiple Excel Files | Excel Discussion (Misc queries) | |||
How to extract data from a wooksheet in a closed workbook | Excel Worksheet Functions |