Improve company productivity with a Business Account.Sign Up

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)Commented:
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)Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best 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)Commented:
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)Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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