Extracting values from spreadsheets

[lovi]

Hello all,

I need some help writing a macro. I'll explain my situation with an example.

I have some workbooks which contain worksheets that look similar to this:

<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width:48pt" span="4" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;width:48pt" height="17" width="64">aa</td> <td class="xl63" style="width:48pt" width="64">bb</td> <td class="xl63" style="width:48pt" width="64">cc</td> <td class="xl63" style="width:48pt" width="64">dd</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">8</td> <td class="xl63">1</td> <td class="xl63">unstable</td> <td class="xl63">16</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">8</td> <td class="xl63">2</td> <td class="xl63">unstable</td> <td class="xl63">32</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">8</td> <td class="xl63">3</td> <td class="xl63">unstable</td> <td class="xl63">48</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">8</td> <td class="xl63">4</td> <td class="xl63">0</td> <td class="xl63">64</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">9</td> <td class="xl63">6</td> <td class="xl63">0</td> <td class="xl63">102</td> </tr> </tbody></table>
Assume that the cell containing 'aa' is at E3. Columns 'aa', 'bb' and 'dd' are results of formulae. Column 'cc' is the result of an IF statement.

In the different sheets, the number of 'unstables' will vary.

How can I write a macro stored in another workbook that will access every workbook(in a folder) and therefore every sheet and extract the values of aa and bb for the row with the last 'unstable'?(in the example, it would be the row with aa=8 and bb=3)

Lovi

 

[sijpie]

how familiar with macros are you?

loop through the sheets:

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
       ...
    Next wsSheet

Find the last occurance of 'Unstable' in column G (is it always in column G?)

   Dim c
   With ws.Range("G:G")
       Set c = .Find("Unstable", LookIn:=xlValues, searchdirection:=xlPrevious)
       If Not c Is Nothing Then   ' Unstable found
           msgbox c.Address & ", " & c.offset(0,-2).value & ", " & c.offset(0,-1).value)
       End If
   End With

 

[lovi]

Hello sijpie, Im a very basic macro user so Im taking it step by step to make sure I understand whats going on.

I have taken an algorithm given to me ages ago on this forum to access workbooks and added your code for accessing worksheets, including a simple function M9=1 for now. While it access the workbook, it is only doing it for one sheet. Could you tell me where Im going wrong?

Thanks
Lovi

Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

Dim wsSheet As Worksheet

sPath = "C:\New folder" 'location of files
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> ""
    Set oWbk = Workbooks.Open(sPath & "\" & sFil)
    For Each wsSheet In Worksheets
   
           Range("M9").FormulaR1C1 = "1"
   
   
    Next wsSheet
    oWbk.Close True
sFil = Dir
Loop
End Sub

 

[sijpie]

are you confusing worksheets with workbooks?

Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
   
           Range("M9").FormulaR1C1 = "1"
   
   
    Next wsSheet

This should go through each sheet in the open workbook and enter 1 into M9

A good article on looping of all sorts can be found here:
http://www.ozgrid.com/VBA/VBALoops.htm

 

[lovi]

Hello Sijpie, the code is successfully opening each sheet and finding the the last 'unstable' ( I have changed 'unstable' to '1'and this works)

Now Im trying to store the copied values in the workbook where the macro is stored (essentially populating this workbook with values from other workbooks). Do you know how I could do this? Also, how can the code be made to store on the next row each time?

Option Explicit
Sub Open_All_Sheets()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim c

Dim wsSheet As Worksheet


sPath = "C:\New folder" 'location of files
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> ""
    Set oWbk = Workbooks.Open(sPath & "\" & sFil)
    For Each wsSheet In oWbk.Worksheets
   
         
            With wsSheet.Range("D:D")
       Set c = .Find("1", LookIn:=xlValues, searchdirection:=xlPrevious)
       If Not c Is Nothing Then   ' Unstable found
       "Range("A2").Value = c.Offset(0, -2).Value
       "Range("B2").Value = c.Offset(0, -1).Value
         
            

       End If
   End With
   
   
    Next wsSheet
    oWbk.Close True
sFil = Dir
Loop
End Sub

 

[sijpie]

You first need to find the last row, store that in a variable, then increment the variable with every rwo written:

Sub Open_All_Sheets()
Dim oWbk As Workbook, oMstr as workbook
Dim sFil As String
Dim sPath As String
Dim c, lRow as Long

Dim wsSheet As Worksheet, wsMstr as Worksheet

Set oMstr = activeWorkbook
Set wsMstr = ActiveSheet

lRow = Range("A65000").end(xlUp).Row + 1

sPath = "C:\New folder" 'location of files
ChDir sPath
sFil = Dir("*.xls")
Do While sFil <> ""
    Set oWbk = Workbooks.Open(sPath & "\" & sFil)
    For Each wsSheet In oWbk.Worksheets
   
         
            With wsSheet.Range("D:D")
       Set c = .Find("1", LookIn:=xlValues, searchdirection:=xlPrevious)
       If Not c Is Nothing Then   ' Unstable found
         oMstr.wsMstr."Range("A" & lRow).Value = c.Offset(0, -2).Value
         oMstr.wsMstr."Range("B" & lRow).Value = c.Offset(0, -1).Value
         lRow = lRow + 1

       End If
   End With
   
   
    Next wsSheet
    oWbk.Close True
sFil = Dir
Loop

End Sub</pre>

 

[lovi]

Hello Sijpie, this is exactly what I was looking for. it is working perfectly. Thank you very much!