Please forgive my lack of brain functions today.

This may sound silly, but Ive been working with access for weeks and cant
wrap my brain around this problem in excel.

Its rather straight forward.
I have a large table some 19000 records.
Each record has 18 columns.
I want to select all records with a value from column # 12 that is equal to
variable €œA1€�.
Then take all matching records and copy to a new workbook and save.
Then do again with different value.

Dan

Hello Dan,

Speaking of Access, using ADO might make some sense with such a quandary.
Here's an example that creates a new workbook in c:\temp\ named Book1.xls:

Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the
variable is located in the 2nd Sheet, A1. The workbook that your running this
from must be saved so that a proper connection can be established.

More info can be found on such an approach he
http://support.microsoft.com/kb/295646/EN-US/

Regards,
Nate Oliver

I need to keep this in excel if I can.

Any ideas?
Let me know.

Dan


"Nate Oliver" wrote:

Hello Dan,

Speaking of Access, using ADO might make some sense with such a quandary.
Here's an example that creates a new workbook in c:\temp\ named Book1.xls:

Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the
variable is located in the 2nd Sheet, A1. The workbook that your running this
from must be saved so that a proper connection can be established.

More info can be found on such an approach he
http://support.microsoft.com/kb/295646/EN-US/

Regards,
Nate Oliver

You can filter data to a new location with Data=Filter=Advanced filter.
I assume A1 was representative. If you need code, turn on the macro
recorder while you do it manually - then turn off the recorder. This
records a single command which should be easy to modify to take variable
arguments and place into a loop.

--
Regards,
Tom Ogilvy

"wtpcomplab" wrote in message
...
I need to keep this in excel if I can.

Any ideas?
Let me know.

Dan


"Nate Oliver" wrote:

Hello Dan,

Speaking of Access, using ADO might make some sense with such a
quandary.
Here's an example that creates a new workbook in c:\temp\ named
Book1.xls:

Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Where Col1 and Col2 are column headers located in row 1 of Sheet1, and
the
variable is located in the 2nd Sheet, A1. The workbook that your running
this
from must be saved so that a proper connection can be established.

More info can be found on such an approach he
http://support.microsoft.com/kb/295646/EN-US/

Regards,
Nate Oliver

"wtpcomplab" wrote:
I need to keep this in excel if I can.

Right, this is all in Excel. Excel can act as a Jet DB as well. I wrote the
code in Excel for Excel and even tested it. One potential issue is that your
data table would really need to look like a DB.

Tom's suggestion is more straightforward although I'm not sure about
looping. You could pass the copied, filtered data to a new workbook, save and
close.

Bon chance,
Nate Oliver

Looping would loop through a list of unique items (also can be built with
advanced filter) and would put each of the items in this list as criteria in
the criteria cell/range, adjust the destination (which would be a variable
in the advanced filter command), then execute the filter. Perform any
remaining administration.

I have successfully used this many times as I am sure you have yourself. So
apparently my suggestion was not clear or was vague as to what I meant.
Thanks for pointing that out.

--
Regards,
Tom Ogilvy


"Nate Oliver" wrote in message
...
"wtpcomplab" wrote:
I need to keep this in excel if I can.

Right, this is all in Excel. Excel can act as a Jet DB as well. I wrote
the
code in Excel for Excel and even tested it. One potential issue is that
your
data table would really need to look like a DB.

Tom's suggestion is more straightforward although I'm not sure about
looping. You could pass the copied, filtered data to a new workbook, save
and
close.

Bon chance,
Nate Oliver

Tom, I apologize, I missed the following in the original post:

Then do again with different value.

Which is exactly where looping comes into play as you have mentioned. Thank
you for following up.

Regards,
Nate Oliver

Nate Oliver wrote:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"

More info on why this is a bad idea can be found he

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)

http://support.microsoft.com/default...;en-us;Q319998

Jamie.

--

Interesting,

Note they're speaking to querying an open Excel file. One of the benefits of
using ADO to query a spreadsheet is that the file can (and apparently should)
be closed.

Not to mention that ADO is extremely fast.

Cheers,
Nate

"Jamie Collins" wrote:


Nate Oliver wrote:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"

More info on why this is a bad idea can be found he

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)

http://support.microsoft.com/default...;en-us;Q319998

Jamie.

--

Nate Oliver wrote:
Note they're speaking to querying an open Excel file.

*You* were suggesting to the OP to query an open Excel file by using
ThisWorkbook.FullName in the connection string.

Jamie.

--

Hello Jamie,

Where's the ADO Recordset in my post?

Quote by MS:
"When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an
Excel worksheet that is open in Excel, a memory leak occurs in the Excel
process. Repeated queries may eventually cause Excel to run out of memory and
raise an error, or cause Excel to stop responding."

Workaround by MS:
"Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel
data to a new worksheet. For additional information about using the SELECT
INTO syntax to export data, click the following article number to view the
article in the Microsoft Knowledge Base:
295646 How To Transfer Data from ADO Data Source to Excel with ADO"

My Original Post:
Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Look familiar?

Regards,
Nate Oliver