MS Query: Break data into separate sheets based on value?

[JB]

Hello,

I have a spreadsheet that is populated from an external
source with a database query (ODBC). My problem is that
the query returns too many rows to fit in a single Excel
sheet.

Is there a programmatic way to split up the data into
separate worksheets, based on the value of a particular
column?

I am familiar with macro programming, although I do not
have a lot of experience. If someone could get me started
in the right direction, I would appreciate it.

TIA,
JB

 

[Dick Kusleika]

JB

I don't think you can do that and have all the data still be part of the
same query. However, you could have multiple queries, one per sheet. If
you have a field that can have one of three values, for example, you could
create three worksheets and put a query on each sheet with the appropriate
criteria so as to limit the records for that sheet.

 

[JB]

Dick,

Thank you for your reply. Your solution would work except
that my particular situation involves too many unique
values (167+) for it to be feasible. Not to mention the
fact that I don't want to hardcode my values.

I think I'm going to change my strategy to run my query
outside of Excel and dump the data into a large text
file. Then I have a macro that will read in the text file
and create worksheets as needed.

JB

 

[Dick Kusleika]

JB

Here's another way for you to consider: Use the CopyFromRecordset method of
the Range object. Here's an example and some explanation.

Sub test()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim DbName As String
Dim sSql As String
Dim i As Long
Dim sh As Worksheet
Const MaxPerSheet As Long = 1000

DbName = "C:\Program Files\Microsoft Office\Office\"
DbName = DbName & "Samples\Northwind.mdb"

sSql = "SELECT Invoices.CustomerID, Invoices.Address, "
sSql = sSql & "Invoices.City, Invoices.OrderID FROM "
sSql = sSql & "`C:\Program Files\Microsoft Office\Office\"
sSql = sSql & "Samples\Northwind`.Invoices Invoices"
sSql = sSql & " ORDER BY Invoices.OrderID"

Set db = DAO.OpenDatabase(DbName)
Set rs = db.OpenRecordset(sSql, dbOpenDynaset)

rs.MoveLast
rs.MoveFirst

For i = 1 To rs.RecordCount Step MaxPerSheet
Set sh = ThisWorkbook.Worksheets.Add
sh.Name = "Record" & i
sh.Range("a1").CopyFromRecordset rs, MaxPerSheet
If i < rs.RecordCount Then
rs.Move 1
End If
Next i

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

First you need to set a reference to a DAO object library. In the VBE, you
go to Tools - References. I used Microsoft DAO 3.51 object library because
my database is Access97. You need to find the proper DAO version for you
database. Access2000 and XP are 3.6 (I think). If you don't know, I would
just start with the biggest version number you have and if you get an error
message, start going lower.

The Const line sets the maximum rows to put in one sheet. I didn't want to
set up a database with a hundred thousand rows, so I made mine 1000. If you
want to fill the sheets, use 65000.

DbName and sSql define the database and sql statement, respectively.
Instead of using the name of a database you can use a connection string and
use the DAO.OpenConnection method. To get the sql statement for your query,
open the query in MSQuery and click the SQL button.

The For loop loops through the recordset (1000 at a time in my example) and
creates a sheet. On that sheet, it uses the CopyFromRecordset method to
copy, but uses the MaxRows arguments to limit the rows copied. Then it
moves one record forward (or you would get a duplicate).

You won't be able to Refresh like a normal External Data Query, but it may
be a better option for you than having to go through a text file.

Let me know if you need more explanation on any of the above.

 

[onedaywhen]

You propose to query the database for a large (66K+ rows) of data, put
them into a text file, then conditionally query the text file to
create worksheets. Are you sure you can't apply the same logic to
conditionally query the *database* to create the worksheets? Isn't
that the point of having a database i.e. to store *all* the data and
only get the rows you need?!

--