Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating code for reading cell values from workbooks in dir
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading & manipulating the Excel data programatically in C# progra | Excel Programming | |||
manipulating formula values | Excel Worksheet Functions | |||
Code for manipulating Lists | Excel Programming | |||
Reading information from all workbooks in a folder... | Excel Programming | |||
Reading formatted cell values into an array | Excel Programming |