* Microsoft Visual Basic 6
* Microsoft SQL Server 2008 R2
* Microsoft Excel 2010
Problem :
Want to export data from SQL Server query to Microsoft Excel
Solution :
1. Example Table,
2. Create Design Form, Example : Form1.frm
3. Create an Command Button
4. Prepare References for Microsoft Excel, go to menu "Projects > References..."
5. Double-click the Command Button "Show To Excel"
6. Next, add additional code for Private Sub Show_To_Excel()
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
Private Sub Command1_Click()
Show_To_Excel
End Sub
Private Sub Show_To_Excel()
Dim ExcelApp As Excel.Application
Dim ExcelWorkBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet
Dim ExcelRange As Excel.Range
Dim FName As Variant
Set Conn_1 = CreateObject("adodb.connection")
Set rs_1 = CreateObject("adodb.recordset")
Conn_1.Open "Provider=sqloledb;Data Source=[DB_SERVER];Initial Catalog=[DATABASE_NAME]; User Id=[USER_NAME]; Password=[PASSWORD];"
Set ExcelApp = CreateObject("Excel.Application")
FName = ExcelApp.GetSaveAsFilename(, "Excel (*.xls), *.xls", , "Save to Excel")
If FName = False Then Exit Sub
Set ExcelWorkBook = ExcelApp.Workbooks.Add
Set ExcelSheet = ExcelWorkBook.Worksheets(1)
ExcelWorkBook.SaveAs FName, xlExcel8
j = 1
ExcelSheet.Cells(j, 1) = "Branch_ID"
ExcelSheet.Cells(j, 2) = "Branch_Name"
rs_1.Open "select * from BRANCH", Conn_1
i = 2
Do While Not rs_1.EOF
ExcelSheet.Cells(i, 1) = rs_1("Branch")
ExcelSheet.Cells(i, 2) = rs_1("Name")
rs_1.MoveNext
i = i + 1
Loop
rs_1.Close
ExcelWorkBook.Save
ExcelWorkBook.Activate
MsgBox "Exporting Successfully...."
ExcelApp.Visible = True
Set rs_1 = Nothing
Set Conn_1 = Nothing
End Sub
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
7. Try to run the code, click "F5" or "Start". Next, click "Show To Excel"
8. Type for filename then click "Save"
9. Here is the results of exporting data from Table Branch
