Re: Permutations - 8 columns

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tim Williams (saxifrax_at_pacbell*dot*net)
Date: 02/21/05 

Date: Sun, 20 Feb 2005 22:10:01 -0800

In that case I might still suggest SQL, but in your case use the Excel
SQL driver and run the query on your extracted data.
If your data is in a straight tabular format with a header row you
could try modifying the code below.

You'll need to add a reference to ADO in your project. Replace "col1"
etc in sSQL with your column headings.

Tim.

Sub Tester()
'run a SQL query against the selected range
Dim rs As ADODB.Recordset
Dim iRow As Integer
Dim sSQL As String

sSQL = "select col1, col2 as v from @ group by col1, col2"
Set rs = GetRecords(Selection, sSQL)

   If Not rs Is Nothing Then
      If Not rs.EOF And Not rs.BOF Then
         Active***.Range("A20").CopyFromRecordset rs
      Else
         MsgBox "No records found"
      End If
   End If

End Sub

Function GetRecords(rng As Range, sSQL As String) As ADODB.Recordset
Const S_TEMP_TABLENAME As String = "SQLtempTable"
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath

   'name the selected range
   On Error Resume Next
   ActiveWorkbook.Names.Item(S_TEMP_TABLENAME).Delete
   If Err.Number <> 0 Then Err.Clear

   On Error GoTo haveError
   ActiveWorkbook.Names.Add Name:=S_TEMP_TABLENAME, RefersToLocal:=rng

   sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & sPath & _
              ";Extended Properties=""Excel 8.0;HDR=Yes"""

   oRS.Open Replace(sSQL, "@", S_TEMP_TABLENAME), oConn
   Set GetRecords = oRS
  Exit Function

haveError:
  MsgBox Err.Description
  Set GetRecords = Nothing

End Function

<sanshah01@yahoo.com> wrote in message
news:1108962109.991919.130800@c13g2000cwb.googlegroups.com...
> Tom,
>
> You are 100% correct in your assumption. We are not allowed to query
> the database directly and exact the information in a tabular form.
>
> However there are standardised reports which we can use to extract
> the
> data. The only problem is that the output is in a report format
> (predefined presentation) and multiple reports need to be run to see
> the whole picture.
>
> The irony is that though we are allowed to see the
> information(whatever
> we want) in a report format, we are not allowed to exact the info
> through SQL in a database format inorder to analyse it in the way we
> want and not in the rigid standarised reports already available.
>
> Regards
> Sandip.
>
> Tim Williams wrote:
>> I took the OP's explanation to mean that owing to the "financial"
>> (sensitive?) nature of the data they were not permitted to make
>> complete "copies" of the data by extracting it whole...
>>
>> Tim.
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:eIKcyK5FFHA.548@TK2MSFTNGP14.phx.gbl...
>> > >We are not allowed to extract the information directly from the
>> > >database
>> >>in a tabular form.
>> >
>> > I suspect if he could query the database, he wouldn't need the
>> > combinations.
>> >
>> > Sounds like he is blindly querying the database with all possible
>> > combinations to get back any possible results that may exists.
>> >
>> > Just my guess of course.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
>> > news:On92jq4FFHA.2568@TK2MSFTNGP10.phx.gbl...
>> >> Since you're using a database, why not just let SQL do the work
>> >> ?
>> >>
>> >> select t.Period, t.account, t.currency, t.code, t.country,[other
>> >> columns here], sum(t.amount),average(t.amount)
>> >> from DataTable t
>> >> group by
>> >> t.Period, t.account, t.currency, t.code, t.country,[other column
>> >> here]
>> >>
>> >> Unless I misunderstand your explanation that should give you the
>> >> desired result.
>> >>
>> >> Tim.
>> >>
>> >>

Quantcast