[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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