Get Data from SQL DB to Excel using VBA script (Macros)

create one button in Excel sheet "Get Data". open sheet in Design view mode. right click on button and select "View Code" option in excel. it will open "Visual Basics" coding window.
My button event is "Private Sub btnGetData_Click()".


please follow the code. for getting the data from SQL db to Excel.
before execution of this code we need to add ADODB class reference in this Visual Basics.

ADD Reference:
in Visual Source window click on Tools--> References --> select "Microsoft ActiveX Data Objects 2.6 Library" in this case i am using Microsoft office 2007 version.

Private Sub btnGetData_Click()

                If Range("C1") = "" Then
                                MsgBox ("Project number required.")
                                Exit Sub
                End If

                Dim SQLConn As ADODB.Connection
                Dim SQLData As ADODB.Recordset

                Set SQLConn = CreateObject("ADODB.Connection")
                SQLConn.Open "provider =sqloledb; Data Source = SampleDataSource; Initial Catalog = Pubs; User Id = Prasad; Password = Prasad@123;"

                Set SQLData = CreateObject("ADODB.Recordset")
                With SQLData
                                ' Assign the Connection object.
                                .ActiveConnection = SQLConn
                                ' Extract the required records.
                                .Open "SELECT [ProjectNo],[Unit],[Customer],[Location],[Project Eng],[Project Eng Loc],[Project Manager],[Team Leader],[Process Eng] FROM [Project] WHERE [ProjectNo]='" & Range("C1") & "'"
                               
                                ' Copy the records into cell A1 on Sheet1.
                               
                                If SQLData.EOF Then
                                                MsgBox ("File Not Found.")
                                Else
                                                SQLData.MoveFirst
                                               
                                                Sheet2.Range("C2").Value = SQLData.Fields.Item("Unit")
                                                Sheet2.Range("C3").Value = SQLData.Fields.Item("Customer")
                                                Sheet2.Range("C4").Value = SQLData.Fields.Item("Location")
                                                Sheet2.Range("C6").Value = SQLData.Fields.Item("Project Eng")
                                                Sheet2.Range("C7").Value = SQLData.Fields.Item("Project Eng Loc")
                                                Sheet2.Range("F5").Value = SQLData.Fields.Item("Project Manager")
                                                Sheet2.Range("F6").Value = SQLData.Fields.Item("Team Leader")
                                                Sheet2.Range("F7").Value = SQLData.Fields.Item("Process Eng")
                                End If
                                ' Tidy up
                                 .Close

                End With

                SQLConn.Close
                Set SQLData = Nothing
                Set SQLConn = Nothing


End Sub


for more SharePoint related Posts please visit "sharepointruler.blogspot.com" blog.

Comments

Post a Comment

Popular posts from this blog

Sending email using Powershell script

Image
 Below is a script that can be used to send email using Powershell. If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )   { Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } $currentDate = Get-Date -Format g $HeaderOne $HeaderTwo $HeaderThree $HeaderFour $Outputreport = "<HTML><TITLE> Test E-Mail notification </TITLE> <head> <style>body {font-family: Calibri;font-size: 14px;}table {border-collapse: collapse;}table, th, td {border: 1px solid black;font-family: Calibri,Verdana;font-size: 14px;}th {background-color: #4CAF50;color:white;}</style></head>                      <BODY>                                           <H2> Test E-Mail notification on $($currentDate) CET </H2>  ...
Read more

Convert List Collection to DataTable using C#

public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)         {             PropertyDescriptorCollection properties =                TypeDescriptor.GetProperties(typeof(T));             System.Data.DataTable table = new System.Data.DataTable();             foreach (PropertyDescriptor prop in properties)                 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);             foreach (T item in data)             {                 DataRow row = table.NewRow();                 foreach (PropertyDescriptor prop in properties)                ...
Read more

Difference Between Site Template and Site Definition

Difference Between Site Template and Site Definition Before going into direct comparison, let me define both: Site Templates  : Are  snapshots of  sites at a point in time. When a user customizes a site from the UI or SPD, the custom template consists of the difference between the original state of the site (determined by its definition) and the state of the site or list when the custom template is generated. Custom templates remain tied to a particular site definition (for example, the site definition for SharePoint Web sites or Meeting Workspace Web sites), so that if the site definition is not present or is changed, the custom template cannot work. Site Definitions  : As the name implies, A Site definition is "core definition of a site”. Each site definition emerges through a combination of files that are placed in the 12 hive of WFE during installation of SharePoint. Site definition files include .xml, .aspx, .ascx, and .master page files, as well as docum...
Read more