[RESOLVED] Formated Excel Sheet, Unk No of Cells, Search

[jkmcgrath]

Hi,

I need to be able to open an already filled in 2003 excel file (.xls) search a column for data and then return the row that matches the criteria.

The sheet has formatting at the top and bottom and is dynamic, it is updated regularly so number of row is an unknown when opening the file. Here is some of the code that I have found by RobDog888 and some others. So I can connect and get specific information from a specified cell but I am lost on how to baiscly perform an SQL statement against it. Any help will be welcomed.

Option Explicit
 
'Add a reference to ms excel xx.0 object library
    Private Sub command1_click()
    Dim oapp As Excel.Application
    Dim oWB As Excel.Workbook
    Set oapp = New Excel.Application
    oapp.Visible = False
    Set oWB = oapp.Workbooks.Open("M:\programming\VB6\reset\R50093.xls")
    MsgBox oapp.Workbooks(1).Sheets("RS-04").Cells(7, 1) 'Cell A1
    
    
Dim topCel As Range, bottomCel As Range
Dim NoOfRows As Integer, a As Integer
 
'Count entries
With ActiveSheet
    Set topCel = .Range("A7")
    Set bottomCel = .Cells((65536), topCel.Column).End(xlUp)
        If bottomCel.Row <= topCel.Row Then
        Exit Sub
        End If
NoOfRows = .Range(topCel, bottomCel).Rows.Count
MsgBox NoOfRows + 6
 
End With
 
oWB.Close
    Set oWB = Nothing
    oapp.Quit
    Set oapp = Nothing
End Sub

[si_the_geek]

First off, don't use ActiveSheet - use the sheet name (or object variable) instead. You should also specify workbooks by name (or object variable) too. Both these issues can lead to your program crashing, or working with the wrong sheet/book. Corrections shown in the code below.

To find a match you can use a loop, eg:

Set oWB = oapp.Workbooks.Open("M:\programming\VB6\reset\R50093.xls")
    MsgBox [b]oWB.Sheets("RS-04")[/b].Cells(7, 1) 'Cell A1
    
Dim topCel As Range, bottomCel As Range
Dim NoOfRows As Integer, a As Integer
 
With [b]oWB.Sheets("RS-04")[/b]
    Set topCel = .Range("A7")
    Set bottomCel = .Cells((65536), topCel.Column).End(xlUp)
        If bottomCel.Row <= topCel.Row Then
        Exit Sub
        End If
NoOfRows = .Range(topCel, bottomCel).Rows.Count
MsgBox NoOfRows + 6
 
 
[b]   'Find your value[/b]
Dim iRow as Integer
Dim iCol as Integer
    iRow = 7
    iCol = 1 'Column to search in 
    Do While .Cells(iRow, 1)  <> ""  'assumes there are values in column 1 for all rows to search
      If .Cells(iRow, iCol) = "[i]your text[/i]" Then
        Msgbox "Match found in row " & iRow & vbCr & "(do something else here)"
      End If
      iRow = iRow + 1
    Loop
 
 
End With

[jkmcgrath]

Roger that Si thanks!!!!!!

I will give that a shot.