Excel boffins
#20
(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.
Reply


Messages In This Thread
Excel boffins - by HawkingsHalfpint - 11-22-2020, 10:50 PM
RE: Excel boffins - by MancBaggie - 11-22-2020, 10:53 PM
RE: Excel boffins - by Big Daddy Cool - 11-22-2020, 10:56 PM
RE: Excel boffins - by SausEggBaton - 11-22-2020, 11:32 PM
RE: Excel boffins - by baggiemike - 11-23-2020, 12:01 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 12:03 AM
RE: Excel boffins - by baggiemike - 11-23-2020, 12:21 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 12:37 AM
RE: Excel boffins - by Fulham Fallout - 11-23-2020, 02:40 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 06:46 AM
RE: Excel boffins - by Anung un Rama - 11-23-2020, 07:16 AM
RE: Excel boffins - by Fulham Fallout - 11-23-2020, 07:15 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 07:35 AM
RE: Excel boffins - by Fulham Fallout - 11-23-2020, 07:41 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 07:47 AM
RE: Excel boffins - by Fulham Fallout - 11-23-2020, 09:03 AM
RE: Excel boffins - by waffle - 11-23-2020, 10:18 AM
RE: Excel boffins - by HawkingsHalfpint - 11-23-2020, 02:22 PM
RE: Excel boffins - by hudds - 11-23-2020, 10:29 AM
RE: Excel boffins - by Fido - 11-23-2020, 10:47 AM
RE: Excel boffins - by Birdman1811 - 11-23-2020, 03:02 PM
RE: Excel boffins - by MassDebater - 11-23-2020, 04:17 PM

Forum Jump:


Users browsing this thread: