Question excel search

[Super Suarez]

Hi Guys,

How do I search an excel spreadheet (temp.xlsx) for a given variable (customer.value)? The variable will be in say field I. Once I find it then I've got to pull that rows data out.

I've managed to open an excel sheet and pick a cell and return customer data by the following, but I can't work out the search part. Not sure if I should be looking at select statements or vlookup or anything else. Here's how far I've got

On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
End If
ExcelApp.Workbooks.Open (folder)
With ExcelApp

' Make Excel invisible through the Application object
.Application.Visible = False

'Insert Excel Data into variable
Customer = .Worksheets(1).Range("D7").Value
UseratEnd = .Worksheets(1).Range("I8").Value
Customer.Value = Customer
CustomerEnd.Value = UseratEnd
[date raised].Value = Date

ExcelApp.Workbooks.Open ("temp.xlsx")

..........


.Application.Quit
End With

Hope someone can help

 

[MLUCKHAM]

I would probably try and be clever and link the excel sheet as a linked table. You can do this dynamically through code at runtime (so you can use a different excel spreadsheet everytime). That way you can use Access queries to do the search or even better just insert the row straight into your query.

Check this code out:-

http://stackoverflow.com/questions/...e-in-ms-access-using-vbscript/3549671#3549671

You can open up excel and loop through the sheet and then the cells, but a linked table would be neater!