Unique Values

[gatarossi]

Dear all,

I'm importing some data from access. In my table, I can have duplicate
data, for example:

accounting_code profit_center period value
10 3 0701 100
10 3 0702 101

In this code below, I'm trying to return in excel all accounting_code
form a determined profit_center, but I need only unique values... How
can i do it?

Public Sub getCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As
ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)

Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile &
";", _
usernm, pword

Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon

End Sub

Sub retorna_valores()

Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
Dim xlsht As Excel.Worksheet

Set xlsht = Sheets("despesas")

sql = "SELECT codigo_conta_contabil from orcamento_despesas "
sql = sql & "Where codigo_centro_custo= '" & Cells(3, 2) & "' ;"

filenm = "C:\Documents and Settings\andre\Meus documentos\despesas
\controle_despesas.mdb"

Call getCn(adoconn, adors, sql, filenm, "", "")

xlsht.Cells(7, 1).CopyFromRecordset adors

adors.Close
adoconn.Close

Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing

End Sub

Thanks a lot!!!

André.

 

[CLR]

I use Jim Cone's fine commercial program called XL Companion for this. It's
available at
http://www.realezsites.com/bus/primitivesoftware/

Vaya con Dios,
Chuck, CABGx3

 

[Harlan Grove]

I use Jim Cone's fine commercial program called XL Companion for this.

....

It may have its merits, but the OP's task is pretty simple.

"(e-mail address removed)" wrote: ....

....

Make the SQL statement

sql = "SELECT codigo_conta_contabil" & vbLF &_
"FROM orcamento_despesas" & vbLF & _
"GROUP BY codigo_conta_contabil, codigo_centro_custo" & vbLF & _
"HAVING codigo_centro_custo = '" & Cells(3, 2) & "' ;"

to fetch only the distinct codigo_conta_contabil entries.