Garraway, Alun
Alun.Garraway at otto.de
Fri Dec 12 09:07:46 CST 2003
this is something I've been working on, might be what your looking for....
you'll need to change it for your needs of course.
here's a short outline:
open recordset (rst) with (in your case) center numbers
start excel und create new workbook
whilst looping through rst add sheets to workbook and name them after center number
and set names of the colums in first row
open 2nd recordset (rstData) and place data in 2nd row
save workbook (workbook will be saved to default location in this case)
hth & have a nice weekend :-)
If you have an questions I'll be back on monday.
alun
Sub ExcelAuto()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQl As String
Dim lngArtNr As Long
Dim oXL As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheets
Dim wsName As String
Dim strSQL_Data As String
Dim rstData As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'open rst with center numbers
strSQl = "SELECT CENTER_NR FROM tblWithCenterNumbers ORDER BY ID;"
rst.Open strSQl, cnn, adOpenForwardOnly, adLockReadOnly
'create an Excel WB
Set oXL = New Excel.Application
oXL.Visible = True
oXL.Workbooks.Add
Set wb = oXL.ActiveWorkbook
Do Until rst.EOF
lngArtNr = rst.Fields("ID")
Debug.Print lngArtNr
'rst with data per
Set rstData = New ADODB.Recordset
strSQL_Data = "SELECT * FROM tblWithData WHERE ID = " & lngArtNr & ";" '!!! is your ID a string or long/int??? !!!!!
rstData.Open strSQL_Data, cnn, adOpenForwardOnly, adLockReadOnly
wsName = lngArtNr
'loop through centers
wb.Worksheets.Add.Name = lngArtNr 'centername/number
wb.Worksheets(wsName).Range("A1") = "col1" 'name cols, optional
wb.Worksheets(wsName).Range("B1") = "col2"
wb.Worksheets(wsName).Range("C1") = "col3"
wb.Worksheets(wsName).Range("D1") = "col4"
wb.Worksheets(wsName).Range("E1") = "col5"
wb.Worksheets(wsName).Range("F1") = "col6"
wb.Worksheets(wsName).Range("G1") = "col7"
wb.Worksheets(wsName).Range("H1") = "col8"
wb.Worksheets(wsName).Range("I1") = "col9"
wb.Worksheets(wsName).Range("J1") = "col10"
wb.Worksheets(wsName).Range("A2") = rstData.Fields("myField1") 'Fill 2nd row with data
wb.Worksheets(wsName).Range("B2") = rstData.Fields("myField2")
wb.Worksheets(wsName).Range("C2") = rstData.Fields("myField3")
wb.Worksheets(wsName).Range("D2") = rstData.Fields("myField4")
wb.Worksheets(wsName).Range("E2") = rstData.Fields("myField5")
wb.Worksheets(wsName).Range("F2") = rstData.Fields("myField6")
wb.Worksheets(wsName).Range("G2") = rstData.Fields("myField7")
wb.Worksheets(wsName).Range("H2") = rstData.Fields("myField8")
wb.Worksheets(wsName).Range("I2") = rstData.Fields("myField9")
wb.Worksheets(wsName).Range("J2") = rstData.Fields("myField10")
rst.MoveNext
Loop
'save workbook and set objects to nothing
wb.SaveAs "alun"
oXL.Quit
Set oXL = Nothing
rst.Close
Set rst = Nothing
rstData.Close
Set rstData = Nothing
cnn.Close
Set cnn = Nothing
End Sub
-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Gina Hoopes
Gesendet: Freitag, 12. Dezember 2003 15:16
An: accessd at databaseadvisors.com
Betreff: 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