Thanks for all the help. Tom's advice worked and the file is now in the
format we need it.

Jamie, I agree your idea is simplier, but since this file has to link into
Business Objects the only way we can get it to work is with Excel and the
green triangles, I tried a text file with text qualifiers and it still
didn't work. I think it's just a Business Objects quirk that it wants the
green triangles to show up, because I had the cells formatted as text and
that's how Excel sees them, but Business Objects would not. Either way, the
reports are still easier to run now than they were 6 months ago.

Thanks again.

Brad
"onedaywhen" wrote in message
oups.com...
A few points:

You can use Jet's SELECT..INTO..FROM syntax to actually create the new
worksheets for you. Also, you should be able to cast/transform you data
in the SELECT clause

SELECT CSTR(MyNumericCol) AS TextCol FROM qryNewExport

to ensure it is the correct data type before it gets to Excel i.e. no
green triangles. In other words, I don't think you need to automate
Excel at all. As a demo, try this:

SELECT *
INTO [Excel 8.0;HDR=YES;Database=C;\MyWorkbook.xls;].ExcelTable1
FROM qryNewExport
WHERE [Revenue Location Name] = '<value here'
;

If you *do* want to automate Excel (e.g. to do some formatting beyond
number formats e.g. color), you can use Excel's CopyFromRecordset to
transfer the data (no headers) in one line of code.

And rather than looping through one recordset and creating the second
on each iteration, you could instead use data shaping to create a
hierarchical recordset in one hit. The main advantage is that you only
need to hit the database once, rather than stay connected to the
database while you are off writing data to Excel. But I note you are
using MS Access, rather than ASP, so hierarchical recordsets could just
be a bit of fun <g. For an example, see:

http://msdn.microsoft.com/library/de...atashaping.asp
Jamie.

--