excuse typos! blimy!

"Patrick Molloy" wrote:

Here's an example. in the develpment environment set a reference (TOOLS/
REFERENCES) to the Microsoft Active Data Objects 2.6 Library and you can use
this code:
Note: My excel workbook (2003) is called Testdatabase.xls and has a table,
name range is testdata. One of the columns, (the 2nd) os called PROD.
So what the code does is to read distinct values from the prod column into
the recordset (rst). Then for each record in rst, it reads all values into a
2nd record set (rst1), then adds a worksheet and names that worksheet,
finally dropping the data into it.


Option Explicit

Sub LoadFromExcelDatabase()

Dim Conn As ADODB.Connection
Dim RST As ADODB.Recordset
Dim RST1 As ADODB.Recordset
Dim strConn As String
Dim SQL As String
Dim ws As Worksheet
Dim cl As Long

Dim sExcelSourceFile As String

sExcelSourceFile = "E:\Excel\Excel_database\Testdatabase.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source="
strConn = strConn & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

Set RST = New ADODB.Recordset
Set RST1 = New ADODB.Recordset
SQL = "SELECT DISTINCT [PROD] FROM testdata"

RST.Open SQL, Conn, adOpenStatic

Do Until RST.EOF
SQL = "SELECT * from testdata where [PROD]='" & RST.Fields(0) & "'"
RST1.Open SQL, Conn, adOpenStatic

Set ws = Worksheets.Add
ws.Name = RST.Fields(0)

For cl = 1 To RST1.Fields.Count
ws.Cells(1, cl).Value = RST1.Fields(cl - 1).Name
Next
ws.Range("A2").CopyFromRecordset RST1
RST1.Close
Set ws = Nothing

RST.MoveNext
Loop
RST.Close
Conn.Close

Set RST = Nothing
Set RST1 = Nothing

Set Con = Nothing

End Sub


"David Macdonald" wrote:

Using ODBC you mean ? I hadn't thought of that.

--
WinXP - Office2003 (Italian)


"Patrick Molloy" wrote:

do you know how to connect to an excel workbook as a database?
if so, your problem is very simple
set populate a records set with "SELECT DISTINCT %column% FROM %table%"
where you replace the two tokens by appropraie values
then for each item in the recordset
you run a "SELECT * FROM %table% WHERE %column%='" & item & "'"

are you ok with this ?


"David Macdonald" wrote:

I have a database in one WorkBook and want to transfer filtered data on
setarate sheets in another WorkBook.
One of the columns in the db is a 5 digit number and the sheets in the
destination Workbook are named with the same numbers.
I want to get all the rows containing the sheet name onto the correct sheets.
i.e. All the rows in WB1 referencing 12345 should be transfered to WB2 sheet
12345, all the rows in WB1 referncing 12346 should be transfered to WB2 sheet
12346, you get the idea...
I could just have Excel switch back and forth between the 2 workbooks,
filter, copy and paste OR (I thought) I could place the whole database in an
array, then filter for the results I need as I step through the different
sheets. Now I have my 3000 row x 16 column array BUT I can't figure how to
filter an array...
Should I just give up and let my users get dizzy watching Excel flick
between 2 workbooks for a couple of minutes ?

--
WinXP - Office2003 (Italian)