Importing Data from another workbook

[Southernw2002]

I want to have a worksheet automatically be populated with the name and several bits of data from other workbooks in a particular folder.

I can get the names to be entered using the INDEX function but currently need to manually edit the path statements to get the extra bits of data.

I cant use the INDIRECT function as the files are closed... and it seems that I cant use INDIRECT.EXT as I am using Excel 2013 64 bit.

Any suggestions ?


Many Thanks

Andrew

[Glenn Kennedy]

I have used INDIRECT.EXT on 2013. There is an additional file that you need to download... I think. However, you can use a UDF to do this for you.

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

Here's a fairly self-explanatory pair of files with it in use. You'll need to adjust the path for the PULL function from C:\Users\Windows 8\Desktop\ - which is what I'm using to whatever you have. Keep Source closed and open PULL Lookup.

Enable macros on opening PULL Lookup....

[Southernw2002]

Hi Glenn .. fantastic .. had not used PULL before and fiddled with you answer and its working like a dream .. really appreciate the help

[Tony Valko]

PULL compliments of Harlan Grove.

Pull

I learned a lot of what I know from Harlan. A true GURU if there ever was one!

[MarvinP]

Hi Andrew,

Take a look at Power Query (called Get & Transform in 2016) as it was built to pull in all different kinds of data into Excel. You should update your profile so we know what version of Excel you have.

[Glenn Kennedy]

It is useful. I copied it from someone else, but I can't remember who it was. So apologies for not acknowledging you.... whoever you are...

[Glenn Kennedy]

Thanks, Tony!!

[Tony Valko]

----------