Getting info from other Excel Files
 

Hi

I wrote to you yesterday and did get some help but unfortunately it did not
quite work and the person had to go home. I am a novice when it comes to
programming in Excel so any help would be appreciated.

I have got the below code which is for a master forecast sheet that takes
information from a number of other files that are closed.

- All the files are the same format (excep the master)
- All files are in same directory
- The Reseller List is as below (but more could be added)
- Running Office 2007 on Vista

Anyway, I get the following results and not sure why 2 work and the other 3
do not.

Alta £108,020.40
Cadassist £78,018.75
Cadspec £0.00
Quadra £0.00
Symetri £0.00

Code is as follows:

Private Sub CmdGetData_Click()

Dim MFGValue, sPath, sFile1, sFile2 As String
Dim ResellerRowm, iRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ResellerRow = 1
iRow = 11

' These are just for my refence
'data location & range to copy
'mydata = "='C:\[Mybook.xls]Sheet1'!$B$2:$F$12"
'mydata = "='F:\Sales\Forecasts\Maxine\[Cadspec
Forecast.xlsm]Summary'!$Y$7:$Y$7"
'mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test\[Symetri
Forecast.xlsm]Summary'!$Y$7:$Y$7"
'mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test\[Cadspec
Forecast.xlsm]Summary'!$Y$7:$Y$7"
'mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t
Test\[Cadassist Forecast.xlsm]Summary'!$Y$7:$Y$7"
'mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test\[Quadra
Forecast.xlsm]Summary'!$Y$7:$Y$7"
'mydata = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test\[Alta
Forecast.xlsm]Summary'!$Y$7:$Y$7"

For ResellerRow = 1 To 5

sPath = "='C:\Users\sreed\Documents\Personal\Excel\Forecas t Test\"
sFile1 = "["
sReseller = Worksheets("Reseller List").Cells(ResellerRow, 1)
sFile2 = " Forecast]Summary'!$Y$7:$Y$7"

MFGValue = sPath & sFile1 & sReseller & sFile2

'link to destination worksheet
With ThisWorkbook.Worksheets("Summary").Range("$D$" & iRow)

.Formula = MFGValue

'convert formula to text
.Value = .Value

End With

iRow = iRow + 1

Application.DisplayAlerts = True

Next ResellerRow

End Sub