Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Re: Store data from an into an array (Read 4784 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Store data from an into an array
28.04.06 at 08:32:16
Hi Yvonne,
Nice of you to confirm.  
When I recorded the initial macro I noticed that the target assignment to litNone was not recorded. I have fixed that in SQL*XL 4.0.44.  
Further, the Dynaset property of the Statement object is not in the documentation. I have only documented the syntax as it is used by the macro recorder. The Dynaset property is my own implementation of the ADO Recordset object. Most properties and functions are available and behave in the same way. I have built my own recordset object as the same syntax also works against OO4O. In my Dynaset object I figure out whether we are connected through ADO or OO4O and I translate the commands to the OO4O equivalents if that is appropriate.
Let me know if you need anything else. Perhaps you can post to us what you have developed. Would be nice to hear.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
YaBB Newbies


Posts: 3
Re: Store data from an into an array
Reply #1 - 02.05.06 at 22:45:16
Here is the codes:
Sub RunQuery()
Dim subcode, message, parttype, usagearea, sheetname As String
Dim MyArray(16, 2) As String
Dim intI As Integer
Dim dyn As Object
message = "Enter Subinventory Code"
subcode = InputBox(message)
usagearea = "FAC"
'Fill array with usage_area sheet names
  SQLXL.Sql.setText "select usg_area,part_type,item_category from xxguycus_inv_part_info where usg_area = '" & usagearea & "' group by usg_area,part_type,item_category order by 1,2 "
  Set SQLXL.Sql.Statements(1).Target = Targets(litNone)
    With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  Set dyn = SQLXL.Sql.Statements(1).Dynaset
  intI = 0
  While Not dyn.EOF
      MyArray(intI, 0) = dyn.Fields("usg_area").Value
      MyArray(intI, 1) = dyn.Fields("part_type").Value
      MyArray(intI, 2) = dyn.Fields("item_category").Value
      intI = intI + 1
'Output the data for each sheet into excel
intI = 0
Do While initI < 17
usagearea = MyArray(initI, 0)
parttype = MyArray(initI, 1)
sheetname = MyArray(initI, 2)
    With SQLXL.Sql
        .appendText "select c.usg_area ua,c.part_type pt, c.part_no pno,d.pdesc,d.uom,round(e.item_cost,2) ""Avg Unit Price"",tot_qoh,"
        .appendText "qoh" & subcode & ",nvl(" & subcode & "usage2003,0) Usage2003,nvl(" & subcode & "usage2004,0) Usage2004,nvl(a." & subcode & "usage2005,0)+nvl(b." & subcode & "usage2005,0) Usage2005 "
        .appendText "from xxguycus_usage_mcs a,xxguycus_usage_orainv b, xxguycus_inv_part_info c,xxguycus_sum_stock_status_v d  ,cst_item_cost_type_v e "
        .appendText "where a.part_no = b.segment1(+) and c.part_no = a.part_no(+) and c.part_no = d.pno and b.inventory_item_id=e.inventory_item_id "
        .appendText "and e.organization_id = '81' and c.usg_area = '" & usagearea & "' and c.part_type='" & parttype & "' "
        .appendText "order by c.usg_area, c.part_type, c.part_no "
    End With
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With Targets(litExcel)
        .AutoFilter = False
        .AutoFit = True
        .Headings = True
        .Sort = False
        .StartFromCell = "'" & sheetname & "'!A6"
        .Transpose = False
        .SQLInNote = True
        .FormatData = True
        .FreezePanes = False
        .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
        .ShowParametersDlg = False
        .ShowResultsetDlg = False
    End With
    initI = initI + 1
  ActiveWindow.SmallScroll Down:=-33
End Sub
The macro selects data from a table and enter it in a 3 dimentional array.  It then loops through the second section of the codes for each rowset in the array, using values from the array as variables.
Thanks for your help
Back to top
Email   IP Logged
Pages: 1