SP1KEMAG00
SP1KEMAG00 at aol.com
Fri Dec 12 18:25:48 CST 2003
Gina,
I've done something similar several times, using the
DoCmd.TransferSpreadsheet command. Despite what it says under Help, if
you export using this command with a value in the [Range]
parameter/option, it'll create a new worksheet with your value as the
sheet name.
The following example loops through a table called 'Categories', updates
an output query and then exports the results. By using the
'CategoryTitle' field from the table, a new sheet is created for each
Title in the table.
I've tested this in Access 97 and Access XP.
Hope this helps,
Shaun
-------------------------------------
Sub ExportToMultipleExcelSheets()
Dim rstCategories As Recordset
Dim strSQL As String
Dim strOutputFile As String
Dim strOutputQry As String
strOutputFile = "C:\CategoryExportTest.xls"
strOutputQry = "qryCategoryOutputQuery"
' If necessary, delete any existing copy of the output file
Kill (strOutputFile)
' This loops through the 'Categories' table, updates the
' output query and then exports it
Set rstCategories = CurrentDb.OpenRecordset("Categories")
With rstCategories
Do Until .EOF
' Update the output queries SQL string
' .Fields(0) is the Category ID number
strSQL = "SELECT Details.Title FROM Details INNER JOIN " & _
"Categories ON Details.CatergoryID =
Categories.CatergoryID " & _
"WHERE (((Details.CatergoryID) = " &
.Fields("CatergoryID").Value & ")) GROUP " & _
"BY Details.Title ORDER BY Details.Title;"
CurrentDb.QueryDefs(strOutputQry).SQL = strSQL
' Using the [RANGE] option of the TransferSpreadsheet command
' creates a new sheet for each Category found
' .Fields(1) is the Category title
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strOutputQry, strOutputFile, False,
.Fields("CatergoryTitle").Value
.MoveNext
Loop
End With
rstCategories.Close
Set rstCategories = Nothing
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gina Hoopes
Sent: 12 December 2003 14:16
To: accessd at databaseadvisors.com
Subject: RE: AW: [AccessD] Export to multiple Excel sheets
No, not yet. Have you got an idea?
Thanks,
Gina
From: "Garraway, Alun" <Alun.Garraway at otto.de>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "Access Developers discussion and problem
solving"<accessd at databaseadvisors.com>
Subject: AW: [AccessD] Export to multiple Excel sheets
Date: Fri, 12 Dec 2003 14:30:33 +0100
hi Gina,
do you have a solution for your problem?
alun
-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Gina Hoopes
Gesendet: Mittwoch, 10. Dezember 2003 20:35
An: accessd at databaseadvisors.com
Betreff: RE: [AccessD] Export to multiple Excel sheets
I'm willing to try anything. At the moment I'm trying to implement
Lambert's suggestion but I'm afraid I don't know enough to fill in the
blanks.
Gina
From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 13:30:19 -0600
If you're going the code route, you may be able to pull this off with
ADO. I have never tried making a new sheet from ADO, in Excel.
Technically it should work, by just using a Make Table query. If you
want to go this route, I'll see if it works or not.
Drew
-----Original Message-----
From: Gina Hoopes [mailto:hoopesg at hotmail.com]
Sent: Wednesday, December 10, 2003 11:54 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
I would like to avoid hard-coding the centers in each dept. since they
can change from year to year, and one of the depts has 64 centers which
even by itself would be a lot of queries.
I'm thinking the solution may be to create a recordset of each dept and
loop through that, creating a recordset within that set for each center.
If that's do-able, I don't know how to properly code the loops or how to
export as it loops through.
Thanks again,
Gina
From: "Heenan, Lambert" <Lambert.Heenan at AIG.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
CC: "'hoopesg at hotmail.com'" <hoopesg at hotmail.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 12:35:59 -0500
If you have a different query for each Center then the export will
create different sheets for each one.
Don't want multiple queries, but would rather use different parameters
for each Center? Ok. Just use CopyObject to create a temporary copy of
the query, export it and then delete the copy.
Lambert
> -----Original Message-----
> From: Gina Hoopes [SMTP:hoopesg at hotmail.com]
> Sent: Wednesday, December 10, 2003 12:14 PM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Export to multiple Excel sheets
>
> You all have been so helpful to me on my projects - I hope one day
I
can
> be
> on the helping end instead of always asking for help.
>
> I've got a table with about 10 fields, 2 of which are Dept and
Center.
> Any
> given Dept might have multiple Centers. I'm exporting query
results to
> Excel spreadsheets, but I haven't been able to figure out how to
have a
> Dept
> with multiple Centers export to one Excel file with a separate
sheet
for
> each Center. Can anyone give me some ideas on how to accomplish
this?
Thanks,
Gina
_________________________________________________________________
Cell phone ‘switch’ rules are taking effect — find out more here.
http://special.msn.com/msnbc/consumeradvocate.armx
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com