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
Coding inner and outer loops (Read 4069 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Coding inner and outer loops
27.05.09 at 22:41:16
Coding inner and outer loops
I'm looking for an example of how to setup concentric loops in a macro with the output of a SQL statement in the outer loop providing the input for a query in the inner loop. So there are 2 (or more) SQL statements and for each row in the outer loop, the inner loop executes using one or more pieces of information field selected in the outer loop's statement.

I'm not finding that in the examples.

Thank you for submitting this excellent question!
I hope I have provided the example you asked for below. Actually iterating over a recordset in a macro is not something that can be recorded as SQL*XL never works this way when SQL is executed from the front-end. Nevertheless it is very well possible as I will describe below.
Test setup:
I have connected to the Northwind database (MS Access) which is included in the test databases collection. It contains an orders and an order details table. Both are joined by the OrderId column.
I want to setup a loop over the Orders table and for each order display the order details.
Recording most of the the code:
As always, I recommend you to record most of your code using the Excel macro recorder. I have switched on the macro recorder and typed the first query as select * from orders, executed the query specified no results and then typed the second query as select * from [Order Details] and let it dump some rows in Excel.  
Tweaking the code:
The code nearly does what I want but I obviously need to setup the loop.
For this I need a variable to hold the recordset (dynaset) of the first query. I introduce a new variable at the top which I have called outer.
Note that I have assigned the Dynaset of the first statement to this variable. Note also, and this is important, that when you run a next setText statement the Statements collection is rebuild. If I would have not saved the dynaset in the outer variable, it would not be available anymore.
Now some further minor tweaks are necessary.  
  • The dynaset needs to be set to the first row: outer.MoveFirst.
  • Then we need to loop until the end of the recordset. We do a while loop as long as the outer.EOF property is true: while outer.EOF
  • We need and end loop too and that should be placed after the second execution statement.
  • The above would create an infinite loop as we have not forwarded the dynaset to the next record yet. Before the Wend statement, make the dynaset move to the next row: outer.MoveNext
  • The second SQL statement needs a minor tweak to only get the order details for the order in the outerloop. The SQL was changed to:
    select * from [Order Details] where OrderId =
  • The coupling between the outer loop and the inner SQL statement is through the OrderId value. This is done by appending the current value of the OrderId column in the outer loop to the inner SQL:
    "select ... where OrderId=" & outer.Fields("OrderId").Value
    I have added a CStr around the value to explicitly converting to text. Never rely on implicit conversions!
  • Finally the cell where the output is expected should be set. This is most easily done by taking the address of the ActiveCell. The ActiveCell is moved after the dump of the details to a cell below the output.
    .StartFromCell = ActiveCell.Address

That's it. Hardly any difficult, is it? And most of the code is generated anyways.


Sub Macro1()
  Dim outer As Object
  SQLXL.Sql.setText "  SELECT *  FROM Orders;"
  Set SQLXL.Sql.Statements(1).Target = Targets(litNone)
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  Set outer = SQLXL.Sql.Statements(1).Dynaset
  While Not outer.EOF
  SQLXL.Sql.setText " SELECT *  FROM [Order Details] where orderid=" & CStr(outer.fields("OrderId").Value)
  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 = ActiveCell.Address
    .Transpose = False
    .SQLInNote = True
    .ShowNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
End Sub
Back to top
« Last Edit: 28.05.09 at 07:48:23 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1