Hi

I tried the following code from John which works OK except it brings up the
open dialogue box. As sson as I select file and OK it poulates the cell in my
master file.

Can I do it without going through the "Open Dialogue" box?

Private Sub CmdGetData_Click()

Dim mydata As String

'data location & range to copy
'<< change as required
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test[Cadassist
Forecast.xlsm]Summary'!$B$2:$B$2"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:B2")
.Formula = mydata

'convert formula to text
.Value = .Value

End With


End Sub



"john" wrote:

Steve,
you should be able to do this using formula without the need to open the file.

try following and see if it helps. Change file name \ folder \ worksheet
name as required

Sub GetData()
Dim mydata As String

'data location & range to copy
'<< change as required
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"

'link to destination worksheet
'<< change as required
With ThisWorkbook.Worksheets(1).Range("B2:F12")
.Formula = mydata

'convert formula to text
.Value = .Value

End With

End Sub
--
jb


"Steve" wrote:

Hi,

I have a number of excel files that my sales team use for forecasting and it
would be great to have a single file where I could pull in all the
information to.

All the forecast sheets are the same so I know the cells that I want to pull
in and I would even know the names.

I'm using Office 2007 with Vista.

Your help or guidance would be appreciated.

Thanks

Steve