Excel as Oracle Report

Sub Click2print()

' to query data and print out

' by Taoyun Zhang

' updated on 2011/05/10

 ' to clear data area

 j = Worksheets("Report").Rows.Count

 Range(Cells(18, 2), Cells(j, 6)).Clear

 

' to get input

  so = Cells(3, 3).Value

  Ln = Cells(4, 3).Value

  ws = Cells(5, 3).Value

  nu = Cells(6, 3).Value

  np = Cells(7, 3).Value

  

  Cells(16, 2).Value = "Distribution Time - " + Format(Now(), "yyyy/mm/dd hh:mm")

  Cells(16, 5).Value = "Number of Unit - " + CStr(nu)

' to match query sql

  sql2 = "SELECT" + Chr(10)

  sql2 = sql2 + "  '" + so + "' Shop_Order," + Chr(10)

  sql2 = sql2 + "  iac.station Work_Station," + Chr(10)

  sql2 = sql2 + "  p.productno Part_Number," + Chr(10)

  sql2 = sql2 + "  tt.extended Part_Description," + Chr(10)

  sql2 = sql2 + "  (iac.qty)*" + CStr(nu) + " Quantity" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  product p," + Chr(10)

  sql2 = sql2 + "  text_translation tt," + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  itemall.station," + Chr(10)

  sql2 = sql2 + "  itemall.productid," + Chr(10)

  sql2 = sql2 + "  SUM(itemall.quantity) qty" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  i.deviatedpart," + Chr(10)

  sql2 = sql2 + "  i.assmworkstation station," + Chr(10)

  sql2 = sql2 + "  i.productid," + Chr(10)

  sql2 = sql2 + "  i.Quantity" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  cob_t_item_assembly i" + Chr(10)

  sql2 = sql2 + "WHERE i.Active = 1" + Chr(10)

  sql2 = sql2 + "AND i.deviatedpart = 0" + Chr(10)

  sql2 = sql2 + "AND i.assmproductionlineno = '" + CStr(Ln) + "'" + Chr(10)

  sql2 = sql2 + "AND i.effectivedate <= SYSDATE" + Chr(10)

  sql2 = sql2 + "AND ((i.discontinuedate IS NULL) OR (i.discontinuedate>SYSDATE))" + Chr(10)

  sql2 = sql2 + "AND i.parentproductid IN" + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  comp_option.productid optionid" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  product prod_so," + Chr(10)

  sql2 = sql2 + "  product_component pc_option," + Chr(10)

  sql2 = sql2 + "  component comp_option" + Chr(10)

  sql2 = sql2 + "WHERE prod_so.productno = '" + so + "'" + Chr(10)

  sql2 = sql2 + "AND pc_option.active = 1" + Chr(10)

  sql2 = sql2 + "AND comp_option.active = 1" + Chr(10)

  sql2 = sql2 + "AND pc_option.productid = prod_so.id" + Chr(10)

  sql2 = sql2 + "AND pc_option.componentid = comp_option.id" + Chr(10)

  sql2 = sql2 + "AND comp_option.effectivedate <= SYSDATE" + Chr(10)

  sql2 = sql2 + "AND ((comp_option.discontinuedate IS NULL) or (comp_option.discontinuedate>SYSDATE))" + Chr(10)

  sql2 = sql2 + ")" + Chr(10)

  sql2 = sql2 + ")" + Chr(10)

  sql2 = sql2 + "Union" + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  item.deviatedpart," + Chr(10)

  sql2 = sql2 + "  item.station," + Chr(10)

  sql2 = sql2 + "  dev.deviationpartid productid," + Chr(10)

  sql2 = sql2 + "  Item.Quantity" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  cob_t_bom_deviation_history dev," + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  i.deviatedpart," + Chr(10)

  sql2 = sql2 + "  i.assmworkstation station," + Chr(10)

  sql2 = sql2 + "  i.productid," + Chr(10)

  sql2 = sql2 + "  i.Quantity" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  cob_t_item_assembly i" + Chr(10)

  sql2 = sql2 + "WHERE i.Active = 1" + Chr(10)

  sql2 = sql2 + "AND i.deviatedpart = 1" + Chr(10)

  sql2 = sql2 + "AND i.assmproductionlineno = '" + CStr(Ln) + "'" + Chr(10)

  sql2 = sql2 + "AND i.effectivedate <= SYSDATE" + Chr(10)

  sql2 = sql2 + "AND ((i.discontinuedate IS NULL) OR (i.discontinuedate>SYSDATE))" + Chr(10)

  sql2 = sql2 + "AND i.parentproductid IN" + Chr(10)

  sql2 = sql2 + "(" + Chr(10)

  sql2 = sql2 + "SELECT" + Chr(10)

  sql2 = sql2 + "  comp_option.productid optionid" + Chr(10)

  sql2 = sql2 + "FROM" + Chr(10)

  sql2 = sql2 + "  product prod_so," + Chr(10)

  sql2 = sql2 + "  product_component pc_option," + Chr(10)

  sql2 = sql2 + "  component comp_option" + Chr(10)

  sql2 = sql2 + "WHERE prod_so.productno = '" + so + "'" + Chr(10)

  sql2 = sql2 + "AND pc_option.active = 1" + Chr(10)

  sql2 = sql2 + "AND comp_option.active = 1" + Chr(10)

  sql2 = sql2 + "AND pc_option.productid = prod_so.id" + Chr(10)

  sql2 = sql2 + "AND pc_option.componentid = comp_option.id" + Chr(10)

  sql2 = sql2 + "AND comp_option.effectivedate <= SYSDATE" + Chr(10)

  sql2 = sql2 + "AND ((comp_option.discontinuedate IS NULL) OR  (comp_option.discontinuedate > SYSDATE))" + Chr(10)

  sql2 = sql2 + ")" + Chr(10)

  sql2 = sql2 + ") item" + Chr(10)

  sql2 = sql2 + "WHERE dev.originalpartid = Item.productid" + Chr(10)

  sql2 = sql2 + "AND dev.effectivestartdate <= SYSDATE" + Chr(10)

  sql2 = sql2 + "AND ((dev.effectiveenddate IS NULL) OR (dev.effectiveenddate > SYSDATE ))" + Chr(10)

  sql2 = sql2 + ")" + Chr(10)

  sql2 = sql2 + ") itemall" + Chr(10)

  sql2 = sql2 + "Group BY" + Chr(10)

  sql2 = sql2 + "  itemall.station," + Chr(10)

  sql2 = sql2 + "  itemall.productid" + Chr(10)

  sql2 = sql2 + ") iac" + Chr(10)

  sql2 = sql2 + "WHERE iac.productid = p.ID" + Chr(10)

  sql2 = sql2 + "AND p.textid = tt.textid" + Chr(10)

  sql2 = sql2 + "AND iac.station LIKE  '" + CStr(ws) + "%'" + Chr(10)

  sql2 = sql2 + "Order BY" + Chr(10)

  sql2 = sql2 + " iac.station," + Chr(10)

  sql2 = sql2 + " p.productno "

   

 ' to query data via adodb connection

 Dim cnn As ADODB.Connection

 Dim rst As ADODB.Recordset

 Set cnn = New ADODB.Connection

 Set rst = New ADODB.Recordset

 cnn.Open "fcswmes", "flxuser", "flxuat"

 rst.ActiveConnection = cnn

 rst.CursorLocation = adUseServer

 rst.Source = sql2

 rst.Open

 

 i = 0

 Do While Not rst.EOF

   Cells(18 + i, 2).Value = rst.Fields(0).Value

   Cells(18 + i, 3).Value = rst.Fields(1).Value

   Cells(18 + i, 4).Value = rst.Fields(2).Value

   Cells(18 + i, 5).Value = rst.Fields(3).Value

   Cells(18 + i, 6).Value = rst.Fields(4).Value

   rst.MoveNext

   i = i + 1

 Loop

 

rst.Close

Set rst = Nothing

Set cnn = Nothing

 

    ' to define table format

    j = Worksheets("Report").Rows.Count

    Range(Cells(17, 2), Cells(17 + i, 6)).Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    

    Columns("C:D").Select

    With Selection

        .HorizontalAlignment = xlLeft

    End With

    

    ' to setup print area

    Worksheets("Report").PageSetup.PrintArea = Range(Cells(10, 2), Cells(17 + i, 6)).Address

    Cells(8, 5).Select

End Sub