[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

use vb (not VBA) to search EXCEL Cells

I am using Visual Basic Express Edition and would like to search an Excel file for a particular Value in a specific cell. Is this possible without getting a brain cramp?
0
evault
Asked:
evault
  • 4
  • 4
1 Solution
 
Wayne Taylor (webtubbs)AstronautCommented:
Hi evault,

Using late binding (ie, no references required)....

        Dim xlApp As Object = CreateObject("Excel.Application")
        Dim xlWB As Object = xlApp.Workbooks.Open("C:\WorkbookPath.xls")
        Dim xlWS As Object = xlWB.Worksheets("Sheet1")
        Dim xlFndCell As Object = xlWS.Cells.Find("the value", , -4163, 1)
        If Not xlFndCell Is Nothing Then
            MsgBox("The value is found in cell " & xlFndCell.Address)
        End If
        xlWB.Close()
        xlApp.Quit()
        xlWS = Nothing
        xlWB = Nothing
        xlApp = Nothing

Regards,

Wayne
0
 
evaultAuthor Commented:
Wayne,

Awesome. It worked. How about searchnig a specific cell, say E:21 for another 150 pts?
0
 
Wayne Taylor (webtubbs)AstronautCommented:
evault,

What do you mean searching for a specific cell? It's always in the same place. To get the value from E21, you'd do something like this....

        Dim xlApp As Object = CreateObject("Excel.Application")
        Dim xlWB As Object = xlApp.Workbooks.Open("C:\WorkbookPath.xls")
        Dim xlWS As Object = xlWB.Worksheets("Sheet1")
        MsgBox "The Value od cell E21 is " & xlWS.Range("E21").Value
        xlWB.Close()
        xlApp.Quit()
        xlWS = Nothing
        xlWB = Nothing
        xlApp = Nothing

Wayne
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
evaultAuthor Commented:
Wayne,

I am looking through 100s of Excel spreadsheets, searching for a spefiic value in cell E21.
0
 
Wayne Taylor (webtubbs)AstronautCommented:
evault,

You'll need to open each of them, and check the value of cell E21 (as I've shown above) versus the value you are looking for.

Wayne
0
 
evaultAuthor Commented:
Wayne,

If I may be so bold, could you recomend a good book for VB. I have 20+ years as a programmer but almost experience with VB (you may have noticed).
0
 
Wayne Taylor (webtubbs)AstronautCommented:
I can't recommend any books, sorry, as I don't use any. My suggestion is to use the F1 key heaps, and ask stacks of questions here on EE.
0
 
evaultAuthor Commented:
Good enough. I can use your snippet to accomplish what I need. Thanks.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now