* 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