D
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!
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!