11-23-2020, 02:22 PM
(This post was last modified: 11-23-2020, 02:23 PM by HawkingsHalfpint.)
(11-23-2020, 10:18 AM)waffle Wrote: This looks like it would probably do the job. Alt+F11 to get into VBA. Insert > Module. Paste this code in. Then....
=VLOOKUP(B2,pull("'C:\Users\Hawks\Docs\[" & A2 & ".xlsx]Sheet1'!A1:B200),2,FALSE)
Or just the pull bit if you need to grab a particular cell.
Code:Function pull(xref As String) As Variant
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If
If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If
If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
pull = Evaluate(xref)
If IsArray(pull) Then Exit Function
If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
On Error Resume Next
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C
pull = r.Value
End If
CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing
End If
End Function
Cheers waffle. Appreciate the help (same to you FF and Mike). I got as far as the ("'C:\Users\Hawks\Docs\[" & A2 & ".xlsx]Sheet1'!A1:B200),2,FALSE) but was using INDIRECT rather than VLOOKUP (which I use for something totally separate).
Loads to try now! To programme that will keep me quiet while the Mrs watches programs about dwarves in LA. Seriously.
Huggs - look away now.

