Click here to Skip to main content
15,892,480 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
I have written a module, but I want to execute the code within the module when I click the button.

VB
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Module Module1

    Public conn As New OleDbConnection()
    Public Filename As String
    Public chkexcel As Boolean
    Public oexcel As Excel.Application
    Public obook As Excel.Workbook
    Public osheet As Excel.Worksheet
    Public R As Integer

    Sub Main()
        Try
            Dbopen()
            'File name and path, here i used abc file to be stored in Bin directory in the sloution directory
            'Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
            Filename = "C:\Users\murli\Desktop\ abc.xls"
            'check if file already exists then delete it to create a new file
            If File.Exists(Filename) Then
                File.Delete(Filename)
            End If
            If Not File.Exists(Filename) Then
                chkexcel = False
                'create new excel application
                oexcel = CreateObject("Excel.Application")
                'add a new workbook
                obook = oexcel.Workbooks.Add
                'set the application alerts not to be displayed for confirmation
                oexcel.Application.DisplayAlerts = True
                'check total sheets in workboob
                Dim S As Integer = oexcel.Application.Sheets.Count()
                'leaving first sheet delete all the remaining sheets
                If S > 1 Then
                    oexcel.Application.DisplayAlerts = False
                    Dim J As Integer = S
                    Do While J > 1
                        oexcel.Application.Sheets(J).delete()
                        J = oexcel.Application.Sheets.Count()
                    Loop
                End If
                'to check the session of excel application
                chkexcel = True


                oexcel.Visible = True
                'this procedure populate the sheet
                Generate_Sheet()
                'save excel file
                obook.SaveAs(Filename)
                'end application object and session
                osheet = Nothing
                oexcel.Application.DisplayAlerts = False
                obook.Close()
                oexcel.Application.DisplayAlerts = True
                obook = Nothing
                oexcel.Quit()
                oexcel = Nothing
                chkexcel = False
                
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Dbclose()
        End Try
    End Sub

    Public Sub Dbopen()
        'open connection for db.mdb stroed in the base directory
        conn.ConnectionString = "data source=xe;provider=oraoledb.oracle.1;user id=system;password=murli"
        conn.Open()
    End Sub
    Public Sub Dbclose()
        'check and close db connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
            conn.Dispose()
            conn = Nothing
        End If
        'check and close excel application
        If chkexcel = True Then
            osheet = Nothing
            oexcel.Application.DisplayAlerts = False
            obook.Close()
            oexcel.Application.DisplayAlerts = True
            obook = Nothing
            oexcel.Quit()
            oexcel = Nothing
        End If
        End
    End Sub
    Sub Generate_Sheet()

        osheet = oexcel.Worksheets(1)
        'rename the sheet
        osheet.Name = "Excel Charts"
        osheet.Range("A1:AZ400").Interior.ColorIndex = 2
        osheet.Range("A1").Font.Size = 12
        osheet.Range("A1").Font.Bold = True
        osheet.Range("A1:I1").Merge()
        osheet.Range("A1").Value = "Modern Pest Control"
        osheet.Range("A1").EntireColumn.AutoFit()
        'format headings
        osheet.Range("A3:G3").Font.Color = RGB(255, 255, 255)
        osheet.Range("A3:G3").Interior.ColorIndex = 5
        osheet.Range("A3:G3").Font.Bold = True
        osheet.Range("A3:G3").Font.Size = 10
        'columns heading
        osheet.Range("A3").Value = "C-No"
        osheet.Range("A3").BorderAround(8)
        osheet.Range("B3").Value = "C-Date"
        osheet.Range("B3").BorderAround(8)
        osheet.Range("C3").Value = "Name"
        osheet.Range("C3").BorderAround(8)
        osheet.Range("D3").Value = "Address"
        osheet.Range("D3").BorderAround(8)
        osheet.Range("E3").Value = "Mobile"
        osheet.Range("E3").BorderAround(8)
        osheet.Range("F3").Value = "Amount"
        osheet.Range("F3").BorderAround(8)
        osheet.Range("G3").Value = "ChemicalUsed"
        osheet.Range("G3").BorderAround(8)
        'populate data from DB
        Dim SQlQuery As String = "select * from pestcontrol"
        Dim SQLCommand As New OleDbCommand(SQlQuery, conn)
        Dim SQlReader As OleDbDataReader = SQLCommand.ExecuteReader
        Dim R As Integer = 3
        While SQlReader.Read
            R = R + 1
            osheet.Range("A" & R).Value = SQlReader.GetValue(0).ToString
            osheet.Range("A" & R).BorderAround(8)
            osheet.Range("B" & R).Value = SQlReader.GetValue(1).ToString
            osheet.Range("B" & R).BorderAround(8)
            osheet.Range("C" & R).Value = SQlReader.GetValue(2).ToString
            osheet.Range("C" & R).BorderAround(8)
            osheet.Range("D" & R).Value = SQlReader.GetValue(3).ToString
            osheet.Range("D" & R).BorderAround(8)
            osheet.Range("E" & R).Value = SQlReader.GetValue(4).ToString
            osheet.Range("E" & R).BorderAround(8)
            osheet.Range("F" & R).Value = SQlReader.GetValue(5).ToString
            osheet.Range("F" & R).BorderAround(8)
            osheet.Range("G" & R).Value = SQlReader.GetValue(6).ToString
            osheet.Range("G" & R).BorderAround(8)
        End While
        SQlReader.Close()
        SQlReader = Nothing
        End Sub
End Module
Posted
Updated 19-Feb-12 20:32pm
v2
Comments
DaveAuld 20-Feb-12 2:33am    
Edit: REMOVED SHOUTING, and wrapped the code block with formatting.
Sergey Alexandrovich Kryukov 20-Feb-12 14:39pm    
Not a question.
--SA

1 solution

Your question is unclear, but if your Module is actually a Console application try adding Console.ReadKey[^] to the top of your Module...
Perhaps the following two lines at the top:
VB
Console.WriteLine("Press any key to start.")
' Wait for the user to actually press a key.
Console.ReadKey
And the following at the bottom of your Main Method:
VB
Console.WriteLine("The work has finished. Press any key to end.")
' Again wait for the user to actually press a key.
Console.ReadKey
 
Share this answer
 
Comments
murlikrishna 21-Feb-12 6:41am    
Sorry Actually am using windows application and i want to run the module when i click the button.
Sander Rossel 21-Feb-12 9:12am    
Then how about calling Module1.Main in you Button.Click EventHandler?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900