[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