Add Data into Excel-2007 With VBA Form

Excel-2007 Macro Enabled File is available to download at the end of page.

When you open this file, it will look like shown in below image.

When you click on button, it will open VBA form which counts last free line and jump on it to insert data.

he Form will look like shown in below image.

To find the coding of this form click on right click on sheet name and select "View Code"

Button is assigned with below macro which then open up the form.

Sub OpenCourseBookingForm()
    UserForm1.Show
End Sub

Below is the function which finds the Last Empty Cell for the given sheet name

Private Function Get_Empty_Cell_Index(ByVal SheetName As String) As Long
    On Error GoTo Last
    Dim Index As Long
    Index = 11
    ActiveWorkbook.Sheets(SheetName).Activate
    Range("A" & Index).Select
    Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
            Index = Index + 1
        End If
    Loop Until IsEmpty(ActiveCell) = True
    Range("A" & Index).Select
    GoTo Last_1
Last:
    MsgBox ("Sheet:" & SheetName & ": Cell No:" & Index & " - " & Err.Number & ":" & Err.Description)
    Index = -1
Last_1:
    Get_Empty_Cell_Index = Index
End Function

while below code is used to count Next Number in sequence for the given sheetname

Private Function Get_NextNo(ByVal SheetName As String) As Long
    Dim NextNo As Long
    ActiveWorkbook.Sheets(SheetName).Activate
    Range("A11").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            NextNo = ActiveCell.Value + 1
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell) = True
    Get_NextNo = NextNo
End Function



Download File VBA Application File

How to Retrieve Data Using Stored Procedure, VB ASP.NET, SQL Server

Below is the  code to create Stored Procedure in SQL Server
procedure is created with name "ActiveFaculties"
------------------------------------------------------------

CREATE PROCEDURE dbo.ActiveFaculties
@Status nchar(10)
AS
SELECT        ID, Name
FROM            Faculty WHERE Status=@Status

Return




Below is the code to diaplaying how to use Stored Procedure to get Data
A page contains one Panel control in which data will be added....
-------------------------------------------------------------------------

Imports System.Data.SqlClient

Partial Class Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Call_Stored_Proc()
    End Sub


    Protected Sub Call_Stored_Proc()
        Dim Cn As SqlConnection
        Dim Cmd As SqlCommand
        Dim Dr As SqlDataReader

        Cn = New SqlConnection("Data Source=ServerName\your database;Initial Catalog=Attendance;Integrated Security=True")
        Cn.Open()
        Try
            With con

                Cmd = New SqlCommand("ActiveFaculties", Cn)
                Cmd.CommandType = Data.CommandType.StoredProcedure
                'Passing parameter to the argument to get specific data...
                Cmd.Parameters.Add("@Status", Data.SqlDbType.Char).Value = "INACTIVE"
                Dr = Cmd.ExecuteReader
                If Dr.HasRows Then
                    While Dr.Read
                        'Add User's ID and Name in Panel1
                        Panel1.Controls.Add(New LiteralControl(Dr("ID") & vbTab & Dr("Name") & "
"))
                    End While
                End If
                Dr.Close()
            End With
        Catch ex As Exception
            Response.Write(ex.Message)'Shows Error , If any
        Finally
            cn.close
        End Try
    End Sub
End Class

---------------------------------------------------------------------------------------------

I hope above code will help you in designing Stored Procedure to retrieve data from the table

I am always open to solve query.....