Export SQL data into Excel Spreadsheet

The simplest way in exporting your SQL database to excel is using the Enterprise Manager SQL 2000

This is a simple accounting application. This will show you how to export your application to Excel using VB6.0

Let us assume that you are now connected to your SQL database. and all you need to do is to get your data information from your SQL and put it in Excel app.



And be sure you already added a reference in your VB6 program to access the Excel App. (refer the pic. above).

On Error GoTo Err_Handler
Dim rs2 As New ADODB.Recordset
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim nrow As Integer

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Get a new workbook.
Set oWB = oXL.Workbooks.Add
Set oSheet = oWB.ActiveSheet

' Add table headers going cell by cell.
With oSheet
.Cells(1, 1).Value = "BR_CODE"
.Cells(1, 2).Value = "N_CHECK"
.Cells(1, 3).Value = "CLAS_C"
.Cells(1, 4).Value = "CLAS_TRD_C"
.Cells(1, 5).Value = "STOR_NO"
.Cells(1, 6).Value = "TSL_NEW_A"
End With

' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "AA1")
.Font.Bold = True
.VerticalAlignment = xlVAlignCenter
End With

Here's some of the format that you can apply in your fields since it wont automatically change the data type you have in your SQL. Don't be confuse about the cell row since my existing program has many fields..you can have your way. But more or less it looks like these.

' Fill date apply format.
 Set oRng = oSheet.Range(oSheet.Cells(3, 27), oSheet.Cells(nrow, 27))
     oRng.NumberFormat = "mm/dd/yy"
' Numeric apply format.
oSheet.Range(oSheet.Cells(3, 9), oSheet.Cells(nrow, 10)).NumberFormat = "00000.00"
oSheet.Range(oSheet.Cells(3, 6), oSheet.Cells(nrow, 8)).NumberFormat = "0000"

and have this at end of the code

 oXL.Visible = True
 oXL.UserControl = True

' Cleanup all objects  (resume next on errors)
Set rs2 = Nothing
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

  Exit Function
Err_Handler:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number

Excel is indeed a great product and will be very useful to our users for the indefinite future.
User can easily manage excel application for reporting purposes.