Extract Range multiple external Workbooks

[Partjob]

I would like to grab data from external work books. I then want to consolidate a range in each workbook on to the original workbook. There are lots of examples of this type of code on here but I am struggling adapting it to my needs All the target workbooks are the same name. ("Spend review tracker ECC.xls") I have this in Cell C3 the file path changes though. The first part is fixed (K:\940 Spend Reviews\38 - Project Spend Review\) I have this in cell A1. The next part is variable, I have this listed in Cells A3:A36. There is then another bit of the file path that is fixed (\Constr\) I have this in Cell B2.

So my file path would be A1&Variable&B2&C3

I have no idea how to build that file path to use it.

Once I have the file path I want to go to the file, extract data in Sheet "Application" Range (I60:T60) without actually opening the file perferably and paste the values in to the original sheet in column D the row being the same row that the variable part of the file path is in.

Now for the more difficult part I think. Not all the paths will be valid at the time the code runs but would be at some time in the future. I believe this means I need an exit sub if the file path is not valid and move to the next file path, again no idea.

The thing is although I have begun to write my own code snippets, I struggle starting off. I generally understand what is going on once it is written.

Thanks a lot
Partjob

 

[Partjob]

Hi jindon
Thanks for getting back to me.
I will explain what is in is of the relevant cells.I have moved things a little bit but that doesn't really matter I had adjusted the code to suit.
In A1 is the first part of the file path (K:\940 Spend Reviews\38 - Project Spend Review\) in A3:A36 is the varable part of the file path for example (210152 - Colchester Grid) or (210153 - Gooseberry Green) then the final part of the file path in B2 (\Constr\[Spend review tracker - ECC.xls]) the cell address was in the code.
I tried all sorts yesterday to solve this playing with this line.

 [B][COLOR=red]If Dir(myDir) <> "" Then
[/COLOR][/B]</pre>

I tried Error, false and all kinds of other permitations but got no where. As I explained in my last postit kind of work it only fails when the path is not valid, although on reading through I have not made my self very clear.
When run as it is, it puts "no such file" in every line. When I comment out the red bold lines it works on the file paths that are valid. When it get to a path that is not valid I have to specify the path via a file navigation window.
I did have to adjust your code ever so slightly as is was getting the path wrong it was putting to many [ ] square brackets in. so MyDir became "A1" & Varable & B1. I do believe the file path structure is OK now.
I do hope this is clear.

Thanks for taking the time to help
Partjob

 

[jindon]

Sub test()
Dim r As Range, myDir As String, fn As String
fn = Mid$(Range("b2").Value, InStrRev(Range("b2").Value,"\") + 1)
For Each r In Range("A3:A36")
    myDir = Range("A1").Value & r.Value & _
    Left$(Range("b2").Value, InStrRev(Range("b2").Value, "\"))
    If Dir(myDir & fn) <> "" Then
        With r.Offset(,3).Resize(,12)
            .Formula = "='" & myDir & fn & "Application'!i60"
            .Value = .Value
        End With
    Else
        r.Offset(,3).Value = "No such file"
    End If
Next
End Sub

 

[Partjob]

Sorry this still is not working properly. logic tells me it should though. If I comment out your bold lines of code. It works on the lines where the file path is valid. When the bold line are not commented out it just put "no such file" in.
Thanks Partjob

 

[jindon]

change to

    If Dir(myDir & Replace(Repalce(fn,"[",""),"]",""))) <> "" Then

 

[Partjob]

I have got no idea how that works but it does or why I have to remove the [ ]
Thanks very much for helping me
Partjob