Linker IT Software
Google
Web www.oraxcel.com
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
Store data from an into an array (Read 5984 times)
yvonner
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Store data from an into an array
26.04.06 at 19:52:34
 
Hi  
I have a macro that uses SQL*XL that is working fine. I would now like to add a loop to this piece of code.  The loop will run for for x amount of times where x depends of the values in a table. I think I can achieve this by defining an array in excel and filling it with the data from the table.  Can anyone assist me in achieving this? Or maybe suggest the best method in doing this?  
 
Thanks
Yvonne
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Store data from an into an array
Reply #1 - 27.04.06 at 14:29:33
 
Hi Yvonne,
 
Yes, you can do this. I am not sure why you want to use an array but you can use that yes.
 
As an example on how to do this I will record a macro to run a select statement. I have recorded this macro in Excel and I have chosen not to receive any output. The macro recorder recorded the following code:
Code:
Sub Macro1()
  SQLXL.Sql.setText "select * from emp"
  Set SQLXL.Sql.Statements(1).Target = Targets(litNone)
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub
 


Now I am going to make use of the Dynaset property of the Statement object. With the MoveFirst method I am making sure the Dynaset is on row 1. I am using the EOF property to see whether I am at the end of the recordset. Each time I am calling MoveNext to get to the next row. The Fields property can be used to get access to the field object.  
For the example I am summing here the salary field, showing the sum of all salaries at the end of the loop.
 
Code:
Sub Macro2()
  SQLXL.Sql.setText "select * from emp"
  Set SQLXL.Sql.Statements(1).Target = Targets(litNone)
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
  
  Dim dblSal As Double
  Dim dyn As Object
  
  Set dyn = SQLXL.Sql.Statements(1).Dynaset
  
  dyn.MoveFirst
  While Not dyn.EOF
    dblSal = dblSal + dyn.Fields("sal").Value
    dyn.MoveNext
  Wend
  
  MsgBox "Total salary is " & CStr(dblSal)
End Sub
 

Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Store data from an into an array
Reply #2 - 05.05.06 at 10:41:51
 
Dear Yvonne,
 
It is very nice to see what you did with the SQL.appendText technique. It indeed allows you to make dynamic SQL and submit it again for dumping in Excel.
 
Best regards, Gerrit-Jan
Back to top
 
 

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