[AccessD] Export to multiple Excel sheets

I know how to do the to-ing but not the fro-ing; see the mskb article below

You can also transfer data via ADO Excel OLeDB provider but you would have
to check the Excel Version available on the client( probably with 
getobject createobject method)

Dim oConn As New ADODB.Connection
With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Book1.xls"
    '....
    .Close
End With
oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic

SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in
Excel Workbooks

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973


Gina Hoopes wrote:

> 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
>
> _________________________________________________________________
> Get holiday tips for festive fun. 
> http://special.msn.com/network/happyholidays.armx
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
Marty Connelly
Victoria, B.C.
Canada