Manipulating code for reading cell values from workbooks in dir

[djenzovoort]

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!

 

[Don Guillett]

Does this simple one help?
Sub getfiledata()
Application.ScreenUpdating = False
Dim FN As String
FileLocation = "c:\a\*.xls"
On Error Resume Next
FN = Dir(FileLocation)
i = 1
Do Until FN = ""

' MsgBox FN
Cells(i, 1) = FN
Cells(i, 2).Formula = "='C:\a\[" & FN & "]sheet1'!$A$4"
FN = Dir
i = i + 1
Loop
Application.ScreenUpdating = True
End Sub