Re: Extract Data From Many Workbooks For Calculations
Hey, FWC, here's a technique for extracting data values from a closed workbook, in case you are unfamiliar with it.
Dim Path As String, RemoteValue As String
'Path = "'C:\path to workbook\[TargetWorkbookName.xls]TargetSheetName'!"
Path = "'C:\Excel Documents\[My Investments.xls]Stocks'!"
'Statement format using a cell address:
RemoteValue = ExecuteExcel4Macro(Path & Range("A1").Address(ReferenceStyle:=xlR1C1))
'Statement format using a cell with a defined name:
RemoteValue = [color=red]ExecuteExcel4Macro[/color](Path & "TgtCell1")
I'm no expert at using "ExecuteExcel4Macro" (see VBA Help), but I DO use it a lot to advantage.
The easiest way to use it is to define a name for each target cell in its resident workbook. That simplifies writing the address. But you can also use the cell's address ("A1") as shown in the example.
Note! When writing the path string begin it with one double quote followed by one single quote: "'.
I don't know if you can use it, but at least this gives you an additional tool to stare at.