retrieve cell value from the cosed file
The thought occurred to me that, after obtaining the values by formulae, you
should convert them to values so that they are no longer linked. Suggested is
this:
Sub GetValsByFormula()
Dim FNs As Variant
Dim FN As String, Pth As String
Dim i As Integer
Dim r As Range
On Error GoTo ExitProc
FNs = Application.GetOpenFilename _
("Excel Files(*.xls), *.xls", MultiSelect:=True)
If TypeName(FNs) = "Boolean" Then Exit Sub
FN = Dir(FNs(LBound(FNs)))
Pth = Left(FNs(LBound(FNs)), Len(FNs(LBound(FNs))) - Len(FN))
For i = 1 To UBound(FNs)
FN = Dir(FNs(i))
Cells(i, 1).Formula = "= '" & Pth & "[" & FN & "]Sheet1'!A1"
Cells(i, 2).Formula = "= '" & Pth & "[" & FN & "]Sheet1'!B1"
Next
Set r = Range(Cells(1, 1), Cells(UBound(FNs), 2))
r.Copy
r.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
ExitProc:
End Sub
Regards,
Greg
|