How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
Go Back  Xtreme Visual Basic Talk > > > How can I find a text in an excel range using Visual Basic 2008??


Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2010, 02:24 AM
ilkaygumus ilkaygumus is offline
Newcomer
 
Join Date: Apr 2010
Posts: 10
Unhappy How can I find a text in an excel range using Visual Basic 2008??


Hello friends,
I have a problem.
In VBA I am able to search a text in an excel range like this:

Code:
Workbooks("Book1").Activate
    With Worksheets(1).Range("C1:C10000")
        Set c = .Find(TextBox1.Text, LookIn:=xlValues, LookAt:=xlWhole)
            If Not c Is Nothing Then
            MsgBox ("Found!")
            End If
    End With
I have upgraded to Visual Studio because of the jobs I am to carry out. So I am translating all the codes from "VBA" to "VB.NET" (Visual Basic 2008)
But I haven't been able to use "Find Method" in VB.NET. I am using Microsoft Office XP Standart Edition (2002 Version). I downloaded and installed Office XP PIAs. After that I referenced them in my project. I used the code
Code:
Imports Microsoft.Office.Interop.Excel
But I can't do what I want
Now, how can I use "Find Method" in Visual Basic 2008 to find a text in an excel range in one of my workbooks?
I would really appreciate any help.
Thank you!
Reply With Quote
  #2  
Old 05-12-2010, 05:02 AM
ilkaygumus ilkaygumus is offline
Newcomer
 
Join Date: Apr 2010
Posts: 10
Default

By the way.. I am not using an Excel template. I want to do this search from a button which is placed on a Form .
Reply With Quote
  #3  
Old 05-12-2010, 06:39 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Basically, the Range.Find method will work fine when using VB.NET. What is changed versus VBA, however, is that you will no longer be able to make use of the VBA globals such as 'Workbooks', 'Worksheets', etc.

Instead, you will have to access these properties via the 'Excel.Application' object.

Let's take your code in steps. For starters, Instead of

Code:
Imports Microsoft.Office.Interop.Excel
You will be better off using:

Code:
Imports Excel = Microsoft.Office.Interop.Excel
This way you will have to access all Excel classes via the 'Excel' prefix, which is more consistent with .NET programming convention.

Next, we can restate your code without the use of the VBA globals:

Code:
' Note: required Imports statements:
' Imports System.Runtime.InteropServices
' Imports Excel = Microsoft.Office.Interop.Excel

Dim excelApp As New Excel.Application
excelApp.Visible = True

Dim workbook As Excel.Workbook = excelApp.Workbooks.Open("Book1.xls")
workbook.Activate()

Dim worksheet As Excel.Worksheet = workbook.Worksheets(1)

With worksheet.Range("C1:C10000")
    Dim c As Excel.Range = _
        .Find(TextBox1.Text, _
                LookIn:=Excel.XlFindLookIn.xlValues, _
                LookAt:=Excel.XlLookAt.xlWhole)

    If Not c Is Nothing Then
        MsgBox ("Found!")
    End If
End With

' Cleanup when done.
' For more on cleanup see "Automating Office Programs with VB.NET"
' (http://www.xtremevbtalk.com/showthread.php?t=160433)

GC.Collect()
GC.WaitForPendingFinalizers()

Marshal.FinalReleaseComObject (Worksheet)

workbook.Close(SaveChanges:=False) ' <-- Or True, your choice.
Marshal.FinalReleaseComObject (Workbook)

excelApp.Quit()
Marshal.FinalReleaseComObject (excelApp)
The above is untested, but I think it should work for you.

Since you are just starting out with VB.NET, you also might want to have a read of the Automating Office Programs with VB.NET tutorial.

Hope this helps!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-12-2010 at 06:47 AM.
Reply With Quote
  #4  
Old 05-12-2010, 08:20 AM
ilkaygumus ilkaygumus is offline
Newcomer
 
Join Date: Apr 2010
Posts: 10
Default

Thank you so much Mr. Rosenblum!
I already know the clean-up stuff. But the way you showed is perfect and gives me exactly what I need!
Thank you again!
Reply With Quote
  #5  
Old 05-12-2010, 08:27 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Cool, glad it worked for you!

We always like it when we have a happy customer.

Happy coding, and come back any time...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008?? How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
 
How can I find a text in an excel range using Visual Basic 2008??
How can I find a text in an excel range using Visual Basic 2008??
 
-->