Export to Excel from SQL Database using VB Application

Public cnn As ADODB.Connection

Public rs As ADODB.Recordset

 

Set cnn = New ADODB.Connection

Set rs = New ADODB.Recordset

cnn.ConnectionString = "Data Source=tt;User id=sa;password=sa;Initial Catalog=tocsdb"

cnn.Open

 

 

 

   Dim Wrk As Object

    Dim Sht As Object

    Dim Xls As Object

    Dim rsDisplayProduct As ADODB.Recordset

    Dim excel_row As Integer

   

    Set rsDisplayProduct = New ADODB.Recordset

    Set Xls = CreateObject("Excel.Application")

    Xls.Caption = "TOCS Sales Report"

    Set Wrk = Xls.Workbooks.Add

    Set Sht = Wrk.Worksheets.Add

    Sht.Name = "TOCS Sales Report"

   

    Sht.Columns(1).ColumnWidth = 4

    Sht.Columns(2).ColumnWidth = 22

    Sht.Columns(3).ColumnWidth = 10

    Sht.Columns(4).ColumnWidth = 6

   

    excel_row = 2

    Sht.Cells(excel_row, 1) = "Sales Report (Prelim Vs Final)"

    Sht.Cells(excel_row, 1).Font.Bold = True

   

  

    excel_row = 4

    Sht.Cells(excel_row, 1) = "Final Order No"

    Sht.Cells(excel_row, 2) = "Prelim Order No"

    Sht.Cells(excel_row, 3) = "Order Dt"

    Sht.Cells(excel_row, 4) = "Retailercode"

    Sht.Cells(excel_row, 5) = "RetailerName"

    Sht.Cells(excel_row, 6) = "CSA"

    Sht.Cells(excel_row, 7) = "Product"

    Sht.Cells(excel_row, 8) = "Design"

    Sht.Cells(excel_row, 9) = "Style"

    Sht.Cells(excel_row, 10) = "Size"

    Sht.Cells(excel_row, 11) = "Ordered Qty"

    Sht.Cells(excel_row, 12) = "Confirmed Qty"

    Sht.Cells(excel_row, 13) = "MRP"

    Sht.Cells(excel_row, 14) = "Cost to Retailer"

   

    Sht.Range(Sht.Cells(excel_row, 1), Sht.Cells(excel_row, 14)).Font.Bold = True

    Sht.Range(Sht.Cells(excel_row, 1), Sht.Cells(excel_row, 14)).Borders.ColorIndex = 1

   

    smt = "exec toc_Sales_Report '" & FromDate & "','" & ToDate & "'"

'you can use sql query and put in smt

    If rs.State = 1 Then rs.Close

    rs.CursorLocation = adUseClient

    rs.Open smt, cnn

   

    While rs.EOF = False And rs.BOF = False

        excel_row = excel_row + 1

        Sht.Cells(excel_row, 1) = rs!FinalOrderNum

        Sht.Cells(excel_row, 2) = rs!PrelimOrderNum

        Sht.Cells(excel_row, 3) = rs!OrderDt

        Sht.Cells(excel_row, 4) = rs!RetailerCode

        Sht.Cells(excel_row, 5) = rs!RetailerName

        Sht.Cells(excel_row, 6) = rs!CSA

        Sht.Cells(excel_row, 7) = rs!Product

        Sht.Cells(excel_row, 8) = rs!Design

        Sht.Cells(excel_row, 9) = rs!Style

        Sht.Cells(excel_row, 10) = rs!Size

        Sht.Cells(excel_row, 11) = rs!Ordered_Qty

        Sht.Cells(excel_row, 12) = rs!Confirmed_Qty

        Sht.Cells(excel_row, 13) = rs!MRP

        Sht.Cells(excel_row, 14) = rs!CostToRet

        rs.MoveNext

    Wend

       

    Xls.Visible = True

   

    Set Wrk = Nothing

    Set Sht = Nothing

    Set Xls = Nothing