Hi Guys,

Need some help in manipulating a code;

I have alot of excelsheets in a certain dir. Now i want to collect all
the cellvalues "O76 from Sheet1" from each workbook. (workbooks are
all same layout) and collect them in a new sheet with the filenames
(for a lookup afterwards).

The code works fine except i get messageboxes 1) the file to select,
2) the sheetname (i need the data on sheet1).

I want excel to loop trough the dir instead of asking for
verification;

Here's the code so far:

Sub MakeLinksUsingDir()
Dim myPath As String
Dim WorkFile As String
Dim MyFormula As String
Dim i As Integer


myPath = "z:\Techniek\7_Qesh\PSV berekeningen\Definitieve Berekeningen
\Gasfase\"
i = 1


WorkFile = Dir(myPath & "*.xls")
Do While WorkFile < ""
MsgBox WorkFile


'Generate myFormula through string manipulation
MyFormula = "='" & myPath & "[" & _
WorkFile _
& "]Sheet1'!"
'Set cell formulas
Cells(i, 1).Value = WorkFile
Cells(i, 2).Formula = MyFormula & "O76"
i = i + 1
WorkFile = Dir()
Loop
End Sub


Thanks so much for the help!