Cari Blog Ini

01 April 2013

How To Show or Load Data From SQL Server Table To Microsoft Excel Using Microsoft Visual Basic 6 Application

Apply To:
* Microsoft Visual Basic 6
* Micorosft Excel 2010
* Microsoft SQL Server 2000

Problem: 

Want to show or load data in file Microsoft Excel Using Microsoft Visual Basic 6  Application With Source Data From Table in SQL Server



Solution:



1. Create an example of Project, example name : "PrjShowDataSQL.vbp"

2. Create an form, example : "frmPrjShowDataSQL.frm"


3.
In tab "Project > References...", add for
a. Microsoft ActiveX Data Objects 2.8 Library
b. Microsoft Excel 14.0 Object Library
c. Microsoft Office 14.0 Object Library




4. At Form "frmPrjShowDataSQL.frm", double-clik for Button "cmdShowDataSQL"


5. At code "Private Sub cmdShowDataSQL_Click()", add the code below, 
'----------------------------------------------------------------------------------
'----------------------------------------------------------------------------------

Private Sub cmdShowDataSQL_Click()

'------------------------------------------------------------
'---Create Active Connection
'------------------------------------------------------------
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    
    ' Provide the connection string.
    Dim strConn As String
    
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    
    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=192.168.1.XXX;INITIAL CATALOG=pubs;"
    
    'Use an integrated login.
    strConn = strConn & " User ID=XX; Password=XXXXXX;"
    
    'Now open the connection.
    cnPubs.Open strConn
'------------------------------------------------------------
'------------------------------------------------------------
'------------------------------------------------------------


'------------------------------------------------------------
'---Create Connection To Microsoft Excel
'------------------------------------------------------------
    Rem General Declaration
    Dim objExcel As Excel.Application
    Dim objWorkBook As Excel.Workbook
    Dim objWorkSheet As Excel.Worksheet


    Rem Initialization of variables
    Set objExcel = New Excel.Application
    Set objWorkBook = objExcel.Workbooks.Add
    Set objWorkSheet = objWorkBook.Worksheets("Sheet1")
    Set objWorkSheet = objExcel.ActiveSheet
'------------------------------------------------------------
'------------------------------------------------------------
'------------------------------------------------------------

    
'------------------------------------------------------------
'---Load The Data From SQL Table To Microsoft Excel
'------------------------------------------------------------
    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

    With rsPubs
        ' Assign the Connection object.
        .ActiveConnection = cnPubs
        
        ' Extract the required records.
        .Open "SELECT * from Authors"
        
        ' Copy the records into cell A1 on Sheet1.
        objWorkSheet.Range("A1").CopyFromRecordset rsPubs
        objExcel.Visible = True ' The created excel workbook would be visible.
        objWorkBook.Close (True) 'This will fire the SaveAs dialog box
    
        
        .Close
    End With

    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
'------------------------------------------------------------
'------------------------------------------------------------
'------------------------------------------------------------
    
End Sub
'----------------------------------------------------------------------------------
'----------------------------------------------------------------------------------





6. Try to run the code, click "Start" or go to menu "Run > Start (F5)"

7. Example the application, click the button "Show  To Excel"


8. Fill the name, example : "Results.xlsx", then click "Save"

9. Check the file, 



Source :
* http://support.microsoft.com/default.aspx?scid=kb;en-us;306125&sd=tech
* http://p2p.wrox.com/beginning-vb-6/22635-writing-data-excel-vb6.html