0

I am using the following VBA script to generate some system data based on the date from the cell value of sheets("SOURCE").range("C2").

Is there a way to loop the sql script and repeat the code using the next date in the range (dates will be listed in sheet "SOURCE", column E). Loop should end when a blank cell is reached?

Each set of data should populate beneath the last set of results in sheet "DATA".

Sub DATA()

Sheets("DATA").Select

    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select

Dim SQL As String
Dim orasession As Object
Dim oradatabase As Object
Dim dyprod As Object
Dim Row As Long

Application.ScreenUpdating = False

Set orasession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraSession Object. ( Oracle )
Set oradatabase = orasession.DbOpenDatabase("thal_cnded.world", "bde_rep/report", 0&) 'Create the OraDatabase Object by opening a connection to Oracle.

SQL = SQL & "select (select min(trunc(cb.act_date)) from com_bde_ahp_log cb "
SQL = SQL & "where (cb.prod_mach like 'M%' or cb.prod_mach like 'B%') and m.wcenter = cb.wcenter (+) and cb.prod_plant = 'W' and cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00 ', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS')) as one, "
SQL = SQL & "m.machine as two, "
SQL = SQL & "(select nvl(sum(cb.diff_ok_disc_qty),0) from com_bde_ahp_log cb "
SQL = SQL & "where m.machine = cb.prod_mach And m.wcenter = cb.wcenter And m.prod_plant = cb.prod_plant And cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS')) as three, "
SQL = SQL & "(select nvl(sum(c.change_m),0) "
SQL = SQL & "from (select distinct mach, count((previous_order)) change_m "
SQL = SQL & "from (select c.prod_mach mach, c.act_date, c.part_no, c.wcenter wcenter, m.machgrp machgrp, "
SQL = SQL & "    NVL((select distinct c1.part_no from com_bde_ahp_log c1 "
SQL = SQL & "        where c1.wcenter not like 'D%' and c1.prod_plant = 'W' and c1.ast = 51 and c1.prod_mach||'-'||c1.cavity = c.prod_mach||'-'||c.cavity and rownum = 1 "
SQL = SQL & "        and c1.act_date = "
SQL = SQL & "            (select max(c2.act_date) from com_bde_ahp_log c2 "
SQL = SQL & "                where c2.wcenter not like 'D%' and c2.prod_plant = 'W' and c2.ast = 51 and c2.prod_mach||'-'||c2.cavity = c.prod_mach||'-'||c.cavity "
SQL = SQL & "                and c2.act_date < c.act_date and c2.act_date between c.act_date-0.5 and c.act_date)),'NA') previous_order, p.grpname format "
SQL = SQL & "from machine_master_data m, com_bde_ahp_log c "
SQL = SQL & "left join (select grpname,prodtyp, plant, packtyp from RLS_PROD_GROUP where grpname in ('BD25','BD50','DVD_5','DVD_9','DVD_10','UMD_2','UMD_1')) p on p.prodtyp = c.prodtyp and c.prod_plant = p.plant and substr(c.packtyp,2,1) = substr(p.packtyp,2,1) "
SQL = SQL & "where c.wcenter not like 'D%' "
SQL = SQL & "and c.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and c.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and to_char(c.act_date,'hh24:mi:ss') between (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 1) and (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 2) "
SQL = SQL & "and c.prod_plant = 'W' and c.ast = 51 and m.machine = c.prod_mach and m.wcenter = c.wcenter and m.prod_plant = c.prod_plant "
SQL = SQL & "group by c.prod_mach, c.cavity, c.part_no, c.wcenter, c.act_date, m.machgrp, c.prod_mach, p.grpname order by 1,2) where previous_order != part_no "
SQL = SQL & "group by mach, wcenter, machgrp, format order by 1) c where c.mach = m.machine) as four, "
SQL = SQL & "(select nvl(sum(cb.diff_ok_disc_qty),0) from com_bde_ahp_log cb "
SQL = SQL & "where m.machine = cb.prod_mach And m.wcenter = cb.wcenter And m.prod_plant = cb.prod_plant And cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= (to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:09:59', 'DD/MM/YYYY HH24:MI:SS'))+1) as five, "
SQL = SQL & "(select nvl(sum(c.change_m),0) from (select distinct mach, count((previous_order)) change_m "
SQL = SQL & "from (select c.prod_mach mach, c.act_date, c.part_no, c.wcenter wcenter, m.machgrp machgrp, "
SQL = SQL & "    NVL((select distinct c1.part_no from com_bde_ahp_log c1 "
SQL = SQL & "        where c1.wcenter not like 'D%' and c1.prod_plant = 'W' and c1.ast = 51 and c1.prod_mach||'-'||c1.cavity = c.prod_mach||'-'||c.cavity and rownum = 1 "
SQL = SQL & "        and c1.act_date = (select max(c2.act_date) "
SQL = SQL & "                from com_bde_ahp_log c2 "
SQL = SQL & "                where c2.wcenter not like 'D%' and c2.prod_plant = 'W' and c2.ast = 51 and c2.prod_mach||'-'||c2.cavity = c.prod_mach||'-'||c.cavity "
SQL = SQL & "                and c2.act_date < c.act_date and c2.act_date between c.act_date-0.5 and c.act_date)),'NA') previous_order, p.grpname format "
SQL = SQL & "from machine_master_data m, com_bde_ahp_log c "
SQL = SQL & "left join (select grpname, prodtyp, plant, packtyp from RLS_PROD_GROUP where grpname in ('BD25','BD50','DVD_5','DVD_9','DVD_10','UMD_2','UMD_1')) p on p.prodtyp = c.prodtyp and c.prod_plant = p.plant and substr(c.packtyp,2,1) = substr(p.packtyp,2,1) "
SQL = SQL & "where c.wcenter not like 'D%' "
SQL = SQL & "and c.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and c.act_date <= (to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:09:59', 'DD/MM/YYYY HH24:MI:SS'))+1 "
SQL = SQL & "and ((to_char(c.act_date,'hh24:mi:ss') between (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 5) and '23:59:59') or (to_char(c.act_date,'hh24:mi:ss') between '00:00:00' and (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 6))) "
SQL = SQL & "and c.prod_plant = 'W' and c.ast = 51 and m.machine = c.prod_mach and m.wcenter = c.wcenter and m.prod_plant = c.prod_plant "
SQL = SQL & "group by c.prod_mach, c.cavity, c.part_no, c.wcenter, c.act_date, m.machgrp, c.prod_mach, p.grpname order by 1,2) where previous_order != part_no "
SQL = SQL & "group by mach, wcenter, machgrp, format order by 1) c where c.mach = m.machine) as six "
SQL = SQL & "from machine_master_data m "
SQL = SQL & "where (m.machine like 'M%' or m.machine like 'B%') "
SQL = SQL & "and m.prod_plant = 'W' "
SQL = SQL & "order by length(m.machine), m.machine "

Set dyprod = oradatabase.CreateDynaset(SQL, 0&)

Sheets("DATA").Select
Row = 2
        If Not dyprod.EOF And Not dyprod.bof Then
                dyprod.movefirst
                       Do Until dyprod.EOF
                            Sheets("DATA").Cells(Row, 1).Select
                            ActiveCell.Value = dyprod.Fields("one").Value
                            Sheets("DATA").Cells(Row, 2).Select
                            ActiveCell.Value = dyprod.Fields("two").Value
                            Sheets("DATA").Cells(Row, 3).Select
                            ActiveCell.Value = dyprod.Fields("three").Value
                            Sheets("DATA").Cells(Row, 4).Select
                            ActiveCell.Value = dyprod.Fields("four").Value
                            Sheets("DATA").Cells(Row, 5).Select
                            ActiveCell.Value = dyprod.Fields("five").Value
                            Sheets("DATA").Cells(Row, 6).Select
                            ActiveCell.Value = dyprod.Fields("six").Value
                            dyprod.movenext

                            Row = Row + 1
                        Loop
                End If

End Sub

I did think I could use the following to loop through the date range, but I cannot work out how to link this and the SQL and make the data appear under the last set of results each time?

Sub DateLoop()

    Dim x As Integer

    NumRows = Range("E2", Range("E2").End(xlDown)).Rows.Count

    Range("E2").Select

        For x = 1 To NumRows

        ActiveCell.Offset(1, 0).Select

    Next

End Sub
Improve this question
2
  • That's quite a query you have here. Have you considered making a parameterized stored procedure on the server, to simplify your code and avoid sending concatenated user input directly to the database? Commented Sep 19, 2016 at 15:05
  • Also, FWIW you're using THE slowest possible way to write back query results to a worksheet. Avoid using Select and ActiveCell, work with object references instead - and check to see if an ODBC driver is available for your db provider; you could be using an ADODB recordset instead, and "dump" it in one single operation onto the target worksheet, instead of writing it cell by cell. Commented Sep 19, 2016 at 15:20

1 Answer 1

Reset to default
3

You could get the row count for column "E"

Dim oWS as Worksheet : Set oWS = Worksheets("SOURCE")
Dim intRowCount as Integer : intRowCount = oWS.Cells(Rows.Count, "E").End(xlUp).Row

Now create a For loop just before you assign the value to SQL

For intRow = 1 To intRowCount

Now that you have your loop, change your Sheets("SOURCE").Range("E2") to Sheets("SOURCE").Range("E" & intRow). In your Do Until dyprod.EOF loop, you can now assign the value as Sheets("DATA").Cells(intRow, 1).Value = ...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.