Monthly Archives: October 2011

Color Print your code with Editor Format colors


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/10/31/color-print-your-code-with-editor-format-colors/

 

Here is one link which I found today and I feel that I should share it. Though this application has been discontinued but it still works on Vista and Win 7.

You can use this application to color print your VB6/VB.Net/VBA code.

Name of Application: PrettyCode.Print

Link: http://submain.com/products/prettycode.print.aspx

Hope this helps Smile

VBA/VB.Net/VB6–Click Open/Save/Cancel Button on IE Download window – PART I


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/10/23/vbavb-netvb6click-opensavecancel-button-on-ie-download-window/

Recently, while answering a question in msdn forum, I came across a question when the asker had a query on how to interact with download window which pops up.

Consider this.

Open Internet explorer and navigate to this link

http://spreadsheetpage.com/index.php/file/king_james_bible/

If your IE settings allow then you will get a pop up window like this.

image

So how do we click on “Open“, “Save” or “Cancel” button?

Initially I thought that using API FindWindow, FindWindowEx and Sendmessage would be enough but soon I realized that like normal windows the download window remains unresponsive to SendMessage API. So finally I had to use a workaround and then I was able to click on the Open/Save/Cancel button.

Like you and me, we both have names, similarly windows have “handles” (hWnd), Class etc. Once you know what that hWnd is, it is easier to interact with that window.

Findwindow API finds the hWnd of a particular window by using the class name and the caption of the window (“File Download”) in this case. To test it, here is a sample code

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Dim Ret As Long

Sub Sample()
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Window Found"
    Else
        MsgBox "Window Not Found"
    End If
End Sub

The “Open“, “Save” and “Cancel” buttons are windows in itself but they are child windows of the main window which is “File Download“. That means each one of those will also have a hWnd 🙂 To find the child windows, we don’t use FindWindow but use FindWindowEx. All the three buttons “Open“, “Save” and “Cancel” have the same class which is “ Button”. To test it, here is a sample code.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Dim Ret As Long, ChildRet As Long

Sub Sample()
    '~~> Get the handle of the "File Download" Window
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"
        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not
        If ChildRet <> 0 Then
            MsgBox "Child Window Also Found"
        Else
             MsgBox "Child Window Not Found"
        End If
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Now, how do we get the hWnd of “Open” only? To get the handle of a specified button we need to first get it’s caption so that we can then compare and see if it is the right button. For this we need to use two more API’s GetWindowText and GetWindowTextLength. See the code below.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long

Dim Ret As Long, ChildRet As Long, OpenRet As Long
Dim strBuff As String, ButCap As String

Sub Sample()
    '~~> Get the handle of the "File Download" Window
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"
        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not
        If ChildRet <> 0 Then
            MsgBox "Child Window Found"

            '~~> Get the caption of the child window
            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
            GetWindowText ChildRet, strBuff, Len(strBuff)
            ButCap = strBuff

            '~~> Loop through all child windows
            Do While ChildRet <> 0
                '~~> Check if the caption has the word "Open"
                '~~> For "Save" or "Cancel", replace "Open" with
                '~~> "Save" or "Cancel"
                If InStr(1, ButCap, "Open") Then
                    '~~> If this is the button we are looking for then exit
                    OpenRet = ChildRet
                    Exit Do
                End If

                '~~> Get the handle of the next child window
                ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
            Loop

            '~~> Check if we found it or not
            If OpenRet <> 0 Then
                MsgBox "The Handle of Open Button is : " & OpenRet
            Else
                MsgBox "The Handle of Open Button was not found"
            End If
        Else
             MsgBox "Child Window Not Found"
        End If
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Now we have the handle of the Button that we need. So how do we click it? Under normal circumstances, the following code would have worked but like I mentioned earlier, the IE window remains unresponsive to the SendMessage API and hence the below code will not work as desired.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Const BM_CLICK = &HF5&

Dim Ret As Long, ChildRet As Long, OpenRet As Long
Dim strBuff As String, ButCap As String

Sub Sample()
    '~~> Get the handle of the "File Download" Window
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"
        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not
        If ChildRet <> 0 Then
            MsgBox "Child Window Found"

            '~~> Get the caption of the child window
            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
            GetWindowText ChildRet, strBuff, Len(strBuff)
            ButCap = strBuff

            '~~> Loop through all child windows
            Do While ChildRet <> 0
                '~~> Check if the caption has the word "Open"
                '~~> For "Save" or "Cancel", replace "Open" with
                '~~> "Save" or "Cancel"
                If InStr(1, ButCap, "Open") Then
                    '~~> If this is the button we are looking for then exit
                    OpenRet = ChildRet
                    Exit Do
                End If

                '~~> Get the handle of the next child window
                ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
            Loop

            '~~> Check if we found it or not
            If OpenRet <> 0 Then
                MsgBox "The Handle of Open Button is : " & OpenRet
                '~~> Click the button using Send Message
                SendMessage OpenRet, BM_CLICK, 0, 0
            Else
                MsgBox "The Handle of Open Button was not found"
            End If
        Else
             MsgBox "Child Window Not Found"
        End If
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Since SendMessage refuses to work, we have to find an alternative. The alternative is to programmatically position your mouse cursor over that button and then click it.

To do this we need few more APIs. They are SetWindowPos, SetCursorPos, GetWindowRect, Sleep and mouse_event.

Now when you use this code, it works perfectly. I have commented the code so that you will not have any problem understanding it.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long

Private Declare Sub SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal _
hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As _
Long, ByVal cy As Long, ByVal wFlags As Long)

Private Declare Function SetCursorPos Lib "user32" _
(ByVal X As Integer, ByVal Y As Integer) As Long

Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As RECT) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Declare Sub mouse_event Lib "user32.dll" (ByVal dwFlags As Long, _
ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

'~~> Constants for pressing left button of the mouse
Private Const MOUSEEVENTF_LEFTDOWN As Long = &H2
'~~> Constants for Releasing left button of the mouse
Private Const MOUSEEVENTF_LEFTUP As Long = &H4

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40

Dim Ret As Long, ChildRet As Long, OpenRet As Long
Dim strBuff As String, ButCap As String
Dim pos As RECT

Sub Sample()
    '~~> Get the handle of the "File Download" Window
    Ret = FindWindow(vbNullString, "File Download")

    If Ret <> 0 Then
        MsgBox "Main Window Found"

        '~~> Get the handle of the Button's "Window"
        ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)

        '~~> Check if we found it or not
        If ChildRet <> 0 Then
            MsgBox "Child Window Found"

            '~~> Get the caption of the child window
            strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
            GetWindowText ChildRet, strBuff, Len(strBuff)
            ButCap = strBuff

            '~~> Loop through all child windows
            Do While ChildRet <> 0
                '~~> Check if the caption has the word "Open"
                '~~> For "Save" or "Cancel", replace "Open" with
                '~~> "Save" or "Cancel"
                If InStr(1, ButCap, "Open") Then
                    '~~> If this is the button we are looking for then exit
                    OpenRet = ChildRet
                    Exit Do
                End If

                '~~> Get the handle of the next child window
                ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
            Loop

            '~~> Check if we found it or not
            If OpenRet <> 0 Then
                MsgBox "The Handle of Open Button is : " & OpenRet

                '~~> Retrieve the dimensions of the bounding rectangle of the
                '~~> specified window. The dimensions are given in screen
                '~~> coordinates that are relative to the upper-left corner of the screen.
                GetWindowRect OpenRet, pos

                '~~> Move the cursor to the specified screen coordinates.
                SetCursorPos (pos.Left - 10), (pos.Top - 10)
                '~~> Suspends the execution of the current thread for a specified interval.
                '~~> This give ample amount time for the API to position the cursor
                Sleep 100
                SetCursorPos pos.Left, pos.Top
                Sleep 100
                SetCursorPos (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2

                '~~> Set the size, position, and Z order of "File Download" Window
                SetWindowPos Ret, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
                Sleep 100

                '~~> Simulate mouse motion and click the button
                '~~> Simulate LEFT CLICK
                mouse_event MOUSEEVENTF_LEFTDOWN, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0
                Sleep 700
                '~~> Simulate Release of LEFT CLICK
                mouse_event MOUSEEVENTF_LEFTUP, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0
            Else
                MsgBox "The Handle of Open Button was not found"
            End If
        Else
             MsgBox "Child Window Not Found"
        End If
    Else
        MsgBox "Window Not Found"
    End If
End Sub

Similarly, you can click on “Save” button and save the file to a location of your choice. This mean, you have to repeat the process of FindWindow, FindWindowEx with “Save As” window.

If it is just a matter of saving a file to a desired location then there is a different API altogether which is URLDownloadToFile. I would recommend using this as it is not painful as above Smile

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim strURL As String
    Dim strPath As String

    '~~> URL of the Path
    strURL = "http://spreadsheetpage.com/downloads/xl/king-james-bible.zip"
    '~~> Destination for the file
    strPath = "E:\Users\Siddharth Rout\Desktop\king-james-bible.zip"

    Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

    If Ret = 0 Then
        MsgBox "File successfully downloaded"
    Else
        MsgBox "Unable to download the file"
    End If
End Sub

The above code will work in VBA and VB6.

VB.NET CODE

a) Code to Click the Buttons

Imports System.Runtime.InteropServices
Imports System.Text

Public Class Form1
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Integer

    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Integer, ByVal hWnd2 As Integer, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Integer

    <DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    Private Shared Function GetWindowText(ByVal hwnd As IntPtr, ByVal lpString As StringBuilder, ByVal cch As Integer) As Integer
    End Function

    <DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    Private Shared Function GetWindowTextLength(ByVal hwnd As IntPtr) As Integer
    End Function

    Private Declare Sub SetWindowPos Lib "user32" (ByVal hwnd As Integer, ByVal _
    hWndInsertAfter As Integer, ByVal X As Integer, ByVal Y As Integer, ByVal cx As _
    Integer, ByVal cy As Integer, ByVal wFlags As Integer)

    Private Declare Function SetCursorPos Lib "user32.dll" ( _
    ByVal X As Int32, ByVal Y As Int32) As Boolean

    <DllImport("user32.dll")> _
    Private Shared Function GetWindowRect(ByVal HWND As Integer, ByRef lpRect As RECT) As Boolean
    End Function

    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Integer)

    Private Declare Sub mouse_event Lib "user32.dll" (ByVal dwFlags As Integer, _
    ByVal dx As Integer, ByVal dy As Integer, ByVal cButtons As Integer, ByVal dwExtraInfo As Integer)

    '~~> Constants for pressing left button of the mouse
    Private Const MOUSEEVENTF_LEFTDOWN As Integer = &H2
    '~~> Constants for Releasing left button of the mouse
    Private Const MOUSEEVENTF_LEFTUP As Integer = &H4

    <StructLayout(LayoutKind.Sequential)> Public Structure RECT
        Dim Left As Integer
        Dim Top As Integer
        Dim Right As Integer
        Dim Bottom As Integer
    End Structure

    Const HWND_TOPMOST = -1
    Const HWND_NOTOPMOST = -2
    Const SWP_NOSIZE = &H1
    Const SWP_NOMOVE = &H2
    Const SWP_NOACTIVATE = &H10
    Const SWP_SHOWWINDOW = &H40

    Dim Ret As Integer, ChildRet As Integer, OpenRet As Integer
    Dim strBuff As String, ButCap As String
    Dim pos As RECT

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Get the handle of the "File Download" Window
        Ret = FindWindow(vbNullString, "File Download")

        If Ret <> 0 Then
            MessageBox.Show("Main Window Found")

            '~~> Get the handle of the Button's "Window"
            ChildRet = FindWindowEx(Ret, 0, "Button", vbNullString)

            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                MessageBox.Show("Child Window Found")

                '~~> Get the caption of the child window
                ButCap = GetText(ChildRet)

                '~~> Loop through all child windows
                Do While ChildRet <> 0
                    '~~> Check if the caption has the word "Open"
                    '~~> For "Save" or "Cancel", replace "Open" with
                    '~~> "Save" or "Cancel"
                    If InStr(1, ButCap, "Open") Then
                        '~~> If this is the button we are looking for then exit
                        OpenRet = ChildRet
                        Exit Do
                    End If

                    '~~> Get the handle of the next child window
                    ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                    '~~> Get the caption of the child window
                    ButCap = GetText(ChildRet)
                Loop

                '~~> Check if we found it or not
                If OpenRet <> 0 Then
                    MessageBox.Show("The Handle of Open Button is : " & OpenRet)

                    '~~> Retrieve the dimensions of the bounding rectangle of the
                    '~~> specified window. The dimensions are given in screen
                    '~~> coordinates that are relative to the upper-left corner of the screen.
                    GetWindowRect(OpenRet, pos)

                    '~~> Move the cursor to the specified screen coordinates.
                    SetCursorPos((pos.Left - 10), (pos.Top - 10))
                    '~~> Suspends the execution of the current thread for a specified interval.
                    '~~> This give ample amount time for the API to position the cursor
                    Sleep(100)
                    SetCursorPos(pos.Left, pos.Top)
                    Sleep(100)
                    SetCursorPos((pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2)

                    '~~> Set the size, position, and Z order of "File Download" Window
                    SetWindowPos(Ret, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE)
                    Sleep(100)

                    '~~> Simulate mouse motion and click the button
                    '~~> Simulate LEFT CLICK
                    mouse_event(MOUSEEVENTF_LEFTDOWN, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0)
                    Sleep(700)
                    '~~> Simulate Release of LEFT CLICK
                    mouse_event(MOUSEEVENTF_LEFTUP, (pos.Left + pos.Right) / 2, (pos.Top + pos.Bottom) / 2, 0, 0)
                Else
                    MessageBox.Show("The Handle of Open Button was not found")
                End If
            Else
                MessageBox.Show("Child Window Not Found")
            End If
        Else
            MessageBox.Show("Window Not Found")
        End If

    End Sub
    Public Function GetText(ByVal hWnd As IntPtr) As String
        Dim length As Integer
        If hWnd.ToInt32 <= 0 Then
            Return Nothing
        End If
        length = GetWindowTextLength(hWnd)
        If length = 0 Then
            Return Nothing
        End If
        Dim sb As New System.Text.StringBuilder("", length + 1)

        GetWindowText(hWnd, sb, sb.Capacity)
        Return sb.ToString()
    End Function
End Class

b) Code to download the file

Public Class Form1
    Private Declare Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Integer, _
    ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Integer, ByVal lpfnCB As Integer) As Integer

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim Ret As Integer
        Dim strURL As String
        Dim strPath As String

        '~~> URL of the Path
        strURL = "http://spreadsheetpage.com/downloads/xl/king-james-bible.zip"
        '~~> Destination for the file
        strPath = "E:\Users\Siddharth Rout\Desktop\king-james-bible.zip"

        Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

        If Ret = 0 Then
            MessageBox.Show("File suceesfully downloaded")
        Else
            MessageBox.Show("Unable to download the file")
        End If
    End Sub
End Class

Hope this helps Smile

Edit: Updated

VBA/VB.Net/VB6–Click Open/Save/Cancel Button on IE Download window – PART II

VB.Net Treeview Tutorial – I


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/10/19/vb-net-treeview-tutorial-i/

I am yet to find a website where you can get all the information about TreeView in 1 page!

VB.NET VERSION 2010

Over the next few blogs, I will cover it up gradually.

NOTE: If you find this article helpful and are looking forward for Part II then I would recommend subscribing to this blog so that you get an automatic update by email when I publish it 🙂

In this Part I, I will show you, “How To…”

1) Populate a Treeview from a database

2) Displaying the currently selected item in Treeview

3) Clearing a Treeview

4) Manually Add Items to Treeview

5) Deleting a node from a Treeview

I have also commented the code so that you will not have a problem understanding it. At the end of this article I have put the complete code together Smile


Populate a Treeview from a database


In this section, I will show you how to populate a Treeview from a Delimited File. I will use a CSV as a database. To create a database in CSV, you can take help of Excel. Create the file as shown in the image below and then save it to a location of your choice.

image

Once the data is ready, save the file as a CSV File as shown below.

image

After the database is created, start Visual Studio and create a new Windows Application Project on VB.NET.  Setup your form as shown in the image below.

image

Description of the above form

The “Load File” button is to select the file which will act as input

Upload Data” button will automatically populate the Treeview from the csv file.

Reset Treeview” button will clear the data in the Treeview

Add Root” button will add a root level node

Add Level 1” and “Add Level 2” will simply add nodes and child nodes.

Label1” will display the currently selected node

and “OpenFileDialog1” will help us in selecting the file which will act as input

Ok! Now we are all set to code!

Open the code Editor and declare this at the very top

Imports System.Data.OleDb

Declare the below variables at the form level as shown in the image below

Dim Root_Level As TreeNode = Nothing
Dim Level_One As TreeNode = Nothing
Dim Level_Two As TreeNode = Nothing
Dim Level_Three As TreeNode = Nothing

image

And put this code in the “Load File” button’s CLICK EVENT.

    '~~> Load a File
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = ".csv"
            .DereferenceLinks = True
            .Filter = _
             "CSV files (*.csv)|*.csv"
            .Multiselect = False
            .Title = "Select a CSV file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

Put this code in the “Upload Data” button’s CLICK EVENT.

    '~~> Automatically Populate Treeview From a File
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Get just the path from the FilePath\Filename
        Dim DataSource As String = System.IO.Path.GetDirectoryName(TextBox1.Text) & "\"

        '~~> Connection String
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DataSource & _
        ";user id=;password=;Extended Properties=""Text;HDR=YES;FMT=Delimited"""

        '~~> Query String
        Dim tblFields As String = "SELECT * from " & TextBox1.Text

        '~~> Creating Data Set
        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        '~~> Opening Connection and filling the Data Set
        conn.Open()
        oData.Fill(ds, "CSV")
        conn.Close()

        '~~> Setting the Table
        Dim dt As DataTable = ds.Tables(0)

        '~~> Retrieving the 1st Column Name
        Dim twRoot As String = dt.Columns(0).ColumnName

        Root_Level = TreeView1.Nodes.Add(twRoot)    '<~~ MyWorld

        For Each dr As DataRow In dt.Rows
            '~~> Check if the Level_One already exists
            Dim FNode As TreeNode = FindInTreeview(dr("Continent"), TreeView1.Nodes)
            '~~> Check if the Level_Two already exists
            Dim SNode As TreeNode = FindInTreeview(dr("Country"), TreeView1.Nodes)

            If FNode Is Nothing Then '<~~ If not Found
                '~~> Add Continents
                Level_One = Root_Level.Nodes.Add(dr("Continent"))
                '~~> Add Country
                Level_Two = Level_One.Nodes.Add(dr("Country"))
                '~~> Add City
                Level_Three = Level_Two.Nodes.Add(dr("City"))
                'TreeView1.Nodes(0).Nodes.Add(Child1, Child1)
            Else '<~~ If Found
                If SNode Is Nothing Then
                    '~~> Add Country
                    Level_Two = Level_One.Nodes.Add(dr("Country"))
                    '~~> Add City
                    Level_Three = Level_Two.Nodes.Add(dr("City"))
                Else
                    '~~> Add City
                    Level_Three = Level_Two.Nodes.Add(dr("City"))
                End If
            End If
        Next

        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

And finally declare this below function at the end as shown in the image below.

    '~~> Function to Find a node in Treeview
    Function FindInTreeview(ByVal strSearch As String, ByVal Nodes As TreeNodeCollection) As TreeNode
        Dim ret As TreeNode

        '~~> Loop through each TreeNode
        For Each TrNode As TreeNode In Nodes
            '~~> Compare node text with search text
            If TrNode.Text = strSearch Then
                Return TrNode
            End If

            '~~> Do recursive search if there are child nodes
            If TrNode.Nodes.Count > 0 Then
                ret = FindInTreeview(strSearch, TrNode.Nodes)
                If Not ret Is Nothing Then
                    Return ret
                End If
            End If
        Next
        Return Nothing
    End Function

image

Now we are all set and ready to test the code.

Run the project and choose the CSV file that we created. Once selected, Click on “Upload Data” button. You will see that the data gets automatically populated as shown in the image below.

image

Now let’s move on to our next section.


Displaying the currently selected item in Treeview


This is the easiest part!

Paste this code in the TreeView’s AFTERCLICK EVENT.

    Private Sub TreeView1_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) _
    Handles TreeView1.AfterSelect
        Label1.Text = TreeView1.SelectedNode.FullPath
    End Sub

Now when you click on the Treeview Node, you will see the currently selected item in “Label1”. See Image below

image

Moving on to our next section.


Clearing a Treeview


Another easy part!

Paste this code in the “Reset Treeview” button’s CLICK EVENT.

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button3.Click
        TreeView1.Nodes.Clear()
    End Sub

We will require this to clear the Treeview once we are testing the code for manually adding the data. If you don’t clear the Treeview then you will get unexpected results Smile

Moving on to our next section.


Manually Add Items to Treeview


Paste this code in the “Add Root” button’s CLICK EVENT

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button4.Click
        Root_Level = TreeView1.Nodes.Add(TextBox2.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

Now Simply type in the text box what you want to be in the ROOT LEVEL and click on “Add Root

image

Paste this code in the “Add Level 1” button’s CLICK EVENT

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button5.Click
        '~~> Adding Level 1
        Level_One = Root_Level.Nodes.Add(TextBox3.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

Before you add a Level 1, please ensure that the Root Level has been added so that Treeview knows where to add the node. See image below.

image

Paste this code in the “Add Level 2” button’s CLICK EVENT

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button6.Click
        '~~> Adding Level 2
        Level_Two = Level_One.Nodes.Add(TextBox4.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

Before you add a Level 2, please ensure that the Root Level and Level 1 has been added so that Treeview knows where to add the node. See image below.

image

Similarly you can “Add” more data to the Treeview.

Moving on to our next section.


Deleting an node from a Treeview


Paste this code in the “Delete” button CLICK EVENT

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button7.Click
        '~~> Deleting selected Level
        If Not TreeView1.SelectedNode Is Nothing Then
            TreeView1.SelectedNode.Remove()
            Label1.Text = ""
        Else
            MessageBox.Show("Please select a node first")
        End If
    End Sub

To delete any node, simply select the node and click on the “Delete” Button.

image

image


Complete Code


Imports System.Data.OleDb

Public Class Form1
    Dim Root_Level As TreeNode = Nothing
    Dim Level_One As TreeNode = Nothing
    Dim Level_Two As TreeNode = Nothing
    Dim Level_Three As TreeNode = Nothing

    '~~> Load a File
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = ".csv"
            .DereferenceLinks = True
            .Filter = _
             "CSV files (*.csv)|*.csv"
            .Multiselect = False
            .Title = "Select a CSV file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Automaticallt Populate Treeview From a File
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Get just the path from the FilePath\Filename
        Dim DataSource As String = System.IO.Path.GetDirectoryName(TextBox1.Text) & "\"

        '~~> Connection String
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DataSource & _
        ";user id=;password=;Extended Properties=""Text;HDR=YES;FMT=Delimited"""

        '~~> Query String
        Dim tblFields As String = "SELECT * from " & TextBox1.Text

        '~~> Creating Data Set
        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        '~~> Opening Connection and filling the Data Set
        conn.Open()
        oData.Fill(ds, "CSV")
        conn.Close()

        '~~> Setting the Table
        Dim dt As DataTable = ds.Tables(0)

        '~~> Retrieving the 1st Column Name
        Dim twRoot As String = dt.Columns(0).ColumnName

        Root_Level = TreeView1.Nodes.Add(twRoot)    '<~~ MyWorld

        For Each dr As DataRow In dt.Rows
            '~~> Check if the Level_One already exists
            Dim FNode As TreeNode = FindInTreeview(dr("Continent"), TreeView1.Nodes)
            '~~> Check if the Level_Two already exists
            Dim SNode As TreeNode = FindInTreeview(dr("Country"), TreeView1.Nodes)

            If FNode Is Nothing Then '<~~ If not Found
                '~~> Add Continents
                Level_One = Root_Level.Nodes.Add(dr("Continent"))
                '~~> Add Country
                Level_Two = Level_One.Nodes.Add(dr("Country"))
                '~~> Add City
                Level_Three = Level_Two.Nodes.Add(dr("City"))
                'TreeView1.Nodes(0).Nodes.Add(Child1, Child1)
            Else '<~~ If Found
                If SNode Is Nothing Then
                    '~~> Add Country
                    Level_Two = Level_One.Nodes.Add(dr("Country"))
                    '~~> Add City
                    Level_Three = Level_Two.Nodes.Add(dr("City"))
                Else
                    '~~> Add City
                    Level_Three = Level_Two.Nodes.Add(dr("City"))
                End If
            End If
        Next

        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

    '~~> Function to Find a node in Treeview
    Function FindInTreeview(ByVal strSearch As String, ByVal Nodes As TreeNodeCollection) As TreeNode
        Dim ret As TreeNode

        '~~> Loop through each TreeNode
        For Each TrNode As TreeNode In Nodes
            '~~> Compare node text with search text
            If TrNode.Text = strSearch Then
                Return TrNode
            End If

            '~~> Do recursive search if there are child nodes
            If TrNode.Nodes.Count > 0 Then
                ret = FindInTreeview(strSearch, TrNode.Nodes)
                If Not ret Is Nothing Then
                    Return ret
                End If
            End If
        Next
        Return Nothing
    End Function

    '~~> Display the current selected node
    Private Sub TreeView1_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) _
    Handles TreeView1.AfterSelect
        Label1.Text = TreeView1.SelectedNode.FullPath
    End Sub

    '~~> Clear the Treeview
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button3.Click
        TreeView1.Nodes.Clear()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button4.Click
        Root_Level = TreeView1.Nodes.Add(TextBox2.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button5.Click
        '~~> Adding Level 1
        Level_One = Root_Level.Nodes.Add(TextBox3.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button6.Click
        '~~> Adding Level 2
        Level_Two = Level_One.Nodes.Add(TextBox4.Text)
        '~~> Expand All Data
        TreeView1.ExpandAll()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button7.Click
        '~~> Deleting selected Level
        If Not TreeView1.SelectedNode Is Nothing Then
            TreeView1.SelectedNode.Remove()
            Label1.Text = ""
        Else
            MessageBox.Show("Please select a node first")
        End If
    End Sub
End Class

Hope this helps. Smile

Charting with VB.Net 2010


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/10/18/charting-with-vb-net-2010/

There are many tutorials on the web regarding VB.Net Charting but I couldn’t find one which actually goes into details.  So I thought of creating one. The example that I will be using retrieves the data from an Access Database and populates the chart in VB.Net.

VB.Net Version: 2010 Ultimate

MS Access : 2010

1) To start off, let’s create a small database in Access. Your Access Database looks like this once it is ready.

imageimage

Name the Table as “Table1” and the Database as “Sample.accdb”. Save it to a location of your choice. Remember the location as we will have to use this database later from vb.net.

2) Start Visual Studio and create a new Windows Application Project on VB.NET. Let’s Call it “Charting”. See snapshot below

image

3) Click ‘OK’ and then place the controls as shown in the image below.

image

In earlier versions of vb.net, you had to actually activate the chart control by right clicking on the “ContextMenuStrip” and then selecting MS Chart Control from the COM Tab. But now you can find that control right under the “Data” Tab.

image

4) Creating Basic Chart: Once your controls are ready, open the code editor and simply paste this code. This code will create a very basic chart.

Imports System.Data.OleDb
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

    '~~> Code required to browse for the Access Database File
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = "accdb"
            .DereferenceLinks = True
            .Filter = _
             "Access files (*.accdb)|*.accdb"
            .Multiselect = False
            .Title = "Select an Access Database file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to generate the chart
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & TextBox1.Text & _
        ";Persist Security Info=False;"
        Dim tblFields As String = "SELECT * from Table1"

        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        conn.Open()
        oData.Fill(ds, "Table1")
        conn.Close()

        Chart1.DataSource = ds.Tables("Table1")
        Dim Series1 As Series = Chart1.Series("Series1")
        Series1.Name = "Sales"
        Chart1.Series(Series1.Name).XValueMember = "nFruits"
        Chart1.Series(Series1.Name).YValueMembers = "nSales"

        Chart1.Size = New System.Drawing.Size(780, 350)
    End Sub

End Class

When you run the code your form will popup and looks as shown in the image below. Select your file by clicking on the “Load File” button. Once you have selected the file, click on “Create Chart

image

When you click on “Create Chart” you see a chart generated as shown below.

image

Now this was making a very basic chart. What if we want it to give a more professional look? What if we wanted to format the title or make the chart look more appealing?

5) Advanced Charting: Now replace the old code with this new code. I have commented the code so you know what the code is doing.

Imports System.Data.OleDb
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

    '~~> Code required to browse for the Access Database File
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = "accdb"
            .DereferenceLinks = True
            .Filter = _
             "Access files (*.accdb)|*.accdb"
            .Multiselect = False
            .Title = "Select an Access Database file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to generate the chart
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Connection String to connect to the access Database
        '~~> If you are planning to use SQL Database then please visit www.connectionstrings.com for an
        '~~> appropriate connection string
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & TextBox1.Text & _
        ";Persist Security Info=False;"

        '~~> Query string
        Dim tblFields As String = "SELECT * from Table1"

        '~~> Connecting to Data base and storing the data in a dataset
        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        conn.Open()
        oData.Fill(ds, "Table1")
        conn.Close()

        '''''''''''''''''''''''''''''
        '~~> SET DATA SOURCE <~~'
        '''''''''''''''''''''''''''''
        Chart1.DataSource = ds.Tables("Table1")

        ''''''''''''''''''''''''''''''''
        '~~> WORKING WITH CHARTAREA <~~'
        ''''''''''''''''''''''''''''''''
        Dim CArea As ChartArea = Chart1.ChartAreas(0)
        CArea.BackColor = Color.Azure           '~~> Changing the Back Color of the Chart Area 
        CArea.ShadowColor = Color.Red           '~~> Changing the Shadow Color of the Chart Area 
        CArea.Area3DStyle.Enable3D = True       '~~> Changing the Chart Style to 3D 

        '~~> Formatting X Axis
        CArea.AxisX.MajorGrid.Enabled = False   '~~> Removed the X axis major grids
        CArea.AxisX.LabelStyle.Font = New System.Drawing.Font("Times New Roman", _
        11.0F, System.Drawing.FontStyle.Italic) '~~> Setting Font, Font Size and Italicizing

        '~~> Formatting Y Axis
        CArea.AxisY.MajorGrid.Enabled = False   '~~> Removed the Y axis major grids
        CArea.AxisY.LabelStyle.Format = "0.00%" '~~> Formatting Y axis to display values in %age
        CArea.AxisY.Interval = 0.2              '~~> Formatting Y axis Interval to 20%

        ''''''''''''''''''''''''''''
        '~~> WORKING WITH TITLE <~~'
        ''''''''''''''''''''''''''''
        '~~> Adding a Title
        Dim T As Title = Chart1.Titles.Add("Sales Report Fruits - Year 2010")
        '~~> Formatting the Title
        With T
            .ForeColor = Color.Black            '~~> Changing the Fore Color of the Title 
            .BackColor = Color.Coral            '~~> Changing the Back Color of the Title 

            '~~> Setting Font, Font Size and Bold/Italicizing
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Underline)
            .BorderColor = Color.Black          '~~> Changing the Border Color of the Title 
            .BorderDashStyle = ChartDashStyle.DashDotDot '~~> Changing the Border Dash Style of the Title 
        End With

        '''''''''''''''''''''''''''''
        '~~> WORKING WITH SERIES <~~'
        '''''''''''''''''''''''''''''
        Dim Series1 As Series = Chart1.Series("Series1")
        '~~> Setting the series Name
        Series1.Name = "Sales"
        '~~> Assigning values to X and Y Axis
        Chart1.Series(Series1.Name).XValueMember = "nFruits"
        Chart1.Series(Series1.Name).YValueMembers = "nSales"
        '~~> Setting Font, Font Size and Bold
        Chart1.Series(Series1.Name).Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
        '~~> Setting Value Type
        Chart1.Series(Series1.Name).YValueType = ChartValueType.Double
        '~~> Setting the Chart Type for Display 
        Chart1.Series(Series1.Name).ChartType = SeriesChartType.Radar
        '~~> Display Data Labels
        Chart1.Series(Series1.Name).IsValueShownAsLabel = True
        '~~> Setting label's Fore Color
        Chart1.Series(Series1.Name).LabelForeColor = Color.DarkGreen
        '~~> Setting label's Format to %age
        Chart1.Series(Series1.Name).LabelFormat = "0.00%"

        '''''''''''''''''''''''''''''
        '~~> WORKING WITH LEGEND <~~'
        '''''''''''''''''''''''''''''
        Dim LG As Legend = Chart1.Legends(0)
        '~~> Changing the Back Color of the Legend 
        LG.BackColor = Color.Wheat
        '~~> Changing the Fore Color of the Legend
        LG.ForeColor = Color.DarkSlateBlue
        '~~> Setting Font, Font Size and Bold
        LG.Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
        '~~> Assigning a title for the legend
        LG.Title = "Legend"

        '~~> Setting the location for the chart
        Chart1.Size = New System.Drawing.Size(780, 350)
    End Sub
End Class

When you run the above code you will see the old chart now transforms into a much more meaningful chart.

image

Similarly we can create other charts like Bar Charts, Pie Charts, Line Charts, Area Charts etc. All you need to do is change the line in the above code

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Column

Few Examples

Line Chart

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Line

image

Pie Chart

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Pie

image

Funnel Chart

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Funnel

image

Radar Chart

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Radar

image

Hope this helps Smile

Find and Replace in Excel using VB.Net


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/?s=Find+and+Replace+in+Excel+using+VB.Net

As we all know that the Find and Replace in Excel is gives us very limited options.

There are certain limitations for example if you look at the image below, you will realize that you can only replace text in formulas. You have use this option to replace text in Values as well but not Comments.

image_thumb1

Now what if you wanted to replace text only in comments. Well you can do that easily by using this example.

First let’s create a sample Excel file for testing. Create a file as shown below and save it to a location of your choice. I am saving it as Sample.xlsx on my desktop.

If you notice that in Cell A1 we have the Sum formula. In D1 We have the Comment and C5 we have some Normal Text. In the example below we will replace

1) “Sum” by say “Max” in the formula

2) “Siddharth” by say “Sid” in the comment and

3) “sample” by “some” in the text.

image

Now let’s create a basic Form with the necessary controls. Please see the below image.

image

I am using the OpenFileDialog1 for my file browsing. Once your form is ready, simply paste the below code.

Also before you use this code, you need to set reference to Excel Object Library. Please see this link on how to do that Smile

https://siddharthrout.wordpress.com/vb-net-and-excel/

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    '~~> Define variables to store Find and Replace values
    Dim ToFind As String, ToReplace As String

    '~~> Code to browse for the Excel File
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        With OpenFileDialog1
            .DefaultExt = "xlsx"
            .DereferenceLinks = True
            .Filter = _
             "Excel files (*.xlsx)|*.xlsx"
            .Multiselect = False
            .Title = "Select an Excel file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox3.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to do a find and replace
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Check if User has selected a file
        If TextBox3.Text = "" Then
            TextBox3.Select()
            MessageBox.Show("Please select a file first")
            Exit Sub
        End If

        '~~> Check if Find Box is filled up
        If TextBox1.Text = "" Then
            TextBox1.Select()
            MessageBox.Show("Please type the word which you want to find")
            Exit Sub
        End If

        '~~> Check if Replace box is filled up
        If TextBox2.Text = "" Then
            TextBox2.Select()
            MessageBox.Show("Please type the word with which you want to replace the above word")
            Exit Sub
        End If

        '~~> Check if user has indicated his/her choice
        If RadioButton1.Checked = False And RadioButton2.Checked = False And RadioButton3.Checked = False Then
            MessageBox.Show("Please indicate whether you want to replace text in formula, value or comment")
            Exit Sub
        End If

        xlWorkBook = xlApp.Workbooks.Open(TextBox3.Text)

        '~~> Replace Sheet1 below by the relevant sheet where you want to do a replace
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Display Excel
        xlApp.Visible = True

        ToFind = TextBox1.Text
        ToReplace = TextBox2.Text

        If RadioButton1.Checked Or RadioButton2.Checked Then
            ReplaceInValuesAndFormulas()
        ElseIf RadioButton3.Checked Then
            ReplaceInComments()
        End If

        '~~> Save the file 
        xlWorkBook.Save()

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Sub to replace in values/formulas
    Sub ReplaceInValuesAndFormulas()
        '~~> Note: If the text is not found then Excel will display a popup box 
        xlWorkSheet.Cells.Replace(
    End Sub

    '~~> Sub to replace in comments
    Sub ReplaceInComments()
        Dim Rng As Excel.Range, aCell As Excel.Range

        '~~> -4144 is for xlCellTypeComments
        Rng = xlWorkSheet.UsedRange.SpecialCells(-4144)
        For Each aCell In Rng.Cells
            aCell.Comment.Text(xlApp.Substitute(aCell.Comment.Text, ToFind, ToReplace))
        Next aCell
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class
In the above code, I am using two different subs. Please see below for more details.

1) Find and Replace Text in Values/Formulas

image

The code that I used is

    '~~> Sub to replace in values/formulas
    Sub ReplaceInValuesAndFormulas()
        '~~> Note: If the text is not found then Excel will display a popup box 
        xlWorkSheet.Cells.Replace(ToFind, ToReplace, 2, 1, False, False, False)
    End Sub

Let’s understand how that works. The syntax of cells.Replace is as shown in the image below

image

Let’s Break it up

Parameters
What
: Required Object. The data to search for. Can be a string or any Microsoft Excel data type.

Replacement: Required Object. The data for replacing. Can be a string or any Microsoft Excel data type.

LookAt : Optional Object. Can be one of the following XlLookAt constants: xlWhole or xlPart. Value of constant  xlWhole is 1 and xlPart is 2

SearchOrder : Optional Object. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns. Value of constant  xlByRows is 1 and xlByColumns is 2

MatchCase : Optional Object. True to make the search case sensitive. The default value is False.

MatchByte : Optional Object. Used only if you’ve selected or installed double-byte language support. True to have double-byte characters match only double-byte characters; False to have double-byte characters match their single-byte equivalents.

SearchFormat : Optional Object. The search format.

ReplaceFormat : Optional Object. The Replace format.

2) Find and Replace Text in Comments

image

    '~~> Sub to replace in comments
    Sub ReplaceInComments()
        Dim Rng As Excel.Range, aCell As Excel.Range

        '~~> -4144 is for xlCellTypeComments
        Rng = xlWorkSheet.UsedRange.SpecialCells(-4144)
        For Each aCell In Rng.Cells
            aCell.Comment.Text(xlApp.Substitute(aCell.Comment.Text, ToFind, ToReplace))
        Next aCell
    End Sub

In the above code what I am doing is looping through all the cells in the sheet which has comments and then searching for the text which needs to be replaced.

You can also see this VBA code and convert it to VB.Net for more flexibility with the above code.

https://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/

Hope this helps Smile