Retrieve data from external Spreadsheets

[Partjob]

I would like to pull data from a lot of external spreadsheets (50 at present but growing by the week) to a single sheet. I have looked through the forums and although there are some threads covering this I do not really understand the code well enough to adapt it to my own needs.

The external sheets are all on the same drive but never in the same folder. They all have different names. The data I want from the external source is always the same range of cells. In this case S279:BB279.

I have some ideas as to how this can be done but firstly I don't know if they would work and secondly even if they did work I do not know how.
My idea is have the path to the file in column A in Sheet1. I would get this by having a cell in the target file showing the full file path, I know how to do this. I would then Paste value this result in to Column A.

Then have the code run though this list picking up the data and Paste value it from Column B across. The macro trigger would be a change in column A as this would be a file path being paste valued in from the target sheet.

So as I see it there would be a loop going through the file names gathering the data, paste value the data on the same row. I may be way off target here and my logic could be flawed. If there is a better or more logical way to achieve this I would appreciate some help.

I have just thought by writing this out that It could be possible to just check the file path being pasted in instead of running through all files everytime. I have feeling that could be a little slow.

I am trying to avoid having lots of active links that I would get if I just Paste linked the cells along with the hassle that would incur.

Sorry for the long post I am just trying to cover all the bases and help whoever tries to help me.
Thanks lot for looking.
Partjob

 

[MDuff]

I think this may be waht you are looking for

http://www.mrexcel.com/forum/showthread.php?t=77475&highlight=Combine

Also for a "user frendly" way check out ASAP Untlitiys It has a Combine option that will walk you trough it

Hope thus helps

 

[Partjob]

Thanks for the reply, Shame I do not understand the thread. I am not sure this will help me. I do not want to import all the files in the dir. there are numerous files that I will have no interest in, this is why I thought it would be a good idea to have the file path listed in column A. I will take it that you have not debunked my idea that it should be possible, I really do have no idea where to start.
Can someone please confirm to me that what I am asking is possble or whether I should forget it and try a different approach.
I have spent hours just ging round in circles on this and really am losing the will to live.
Thanks to anyone who takes the time to help me on this.

Partjob

 

[CharlesH]

PartJob,

The following code looks at column "A" (Sheet1) for the full Path and Name of the workbook you want to open and retrieve info from the range you specified and copy it to a sheet that I named "Master". It should copy the info to the first vacant row in the Master worksheet.
Paste this code to a module

Sub test()
Application.ScreenUpdating = False
Dim Plrow As Long
Dim Mlrow As Long
Dim i As Integer
Dim MyPath As String
Dim Mws As Worksheet
Set Mws = Sheets("Master")
Plrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Mlrow = Mws.Range("A65536").End(xlUp).Row + 1 '' set to last empty row in Master sheet
For i = 2 To Plrow
MyPath = Cells(i, 1).Text ''for path and file name
    ''' Open workbook to get info and copy to sheet2 '''
    Workbooks.Open filename:=MyPath
       ''Now copy cange to master''
    With ActiveWorkbook
        Range("S279:BB279").Copy Destination:=Mws.Cells(Mlrow, 1)
    End With
    ActiveWorkbook.Close
Next i
Application.ScreenUpdating = True
End Sub

 

[Partjob]

I know that most people are looking for solutions to their own problems here so this is how this looked in the end. Thanks to CharlesH for the help he supplied. His solution does work but this is how I adapted, and he helped via email, to suit my needs. This looks at the path being pasted in to column A and then retrieves the data from that file and paste values of the range to the original sheet starting at column G. There is a variable in the copy range to allow for the differing number of months a project would run.

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Myfile  As String
Dim Myrow As Integer
Dim MyWbk As String
'state the start of the copy range
StartCol = 19
'State End Column of copy range looking at how many months
EndCol = Sheet1.Cells(Target.Row, 19).Value
'Build string to allow for variable number of months
CopRan = Chr(StartCol + 64) & 379 & ":" & Chr(EndCol + 64 + 18) & 379
MyWbk = ThisWorkbook.Name
If Target.Column > 1 Then
    Exit Sub
Else
    'Open target file
    Workbooks.Open Filename:=Cells(Target.Row, 1).Text 'place path to workbook in cell
    With ActiveWorkbook
    'Copy range needed on target file
    Sheets("Outline Activity Schedule").Range(CopRan).Copy
    'Paste values on
       Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, 7).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End If
End Sub

Really hope this helps anyone looking to read data from a different workbook.
Partjob