Hello

[Gary]

I can easily export to a new excel spreadsheet. How do I
export to a certain "Sheet3" of an existing spreadsheet
copying over the existing "Sheet3" or deleting it and
creating another "Sheet3". I the end I plan to have
seven sheets that are exporting to from different
databases and the eight sheet will generate totals from
the other seven sheets. Please help. Thanks.

Gary

 

[John Nurick]

Hi Gary,

1) DoCmd.TransferSpreadsheet cannot export to a specific range but can
export to a specific sheet in an existing workbook . Just pass the sheet
name as the Range argument, e.g.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table",
"C:\folder\file.xls", -1, "SheetName"

2) Often it's preferable to use Access to summarise one's data. You can
access data in other databases by using linked tables or by using the
SQL IN clause in queries.

(For help on the latter, go to the contents page of Access help and look
for Microsoft Jet SQL Reference/Data Manipulation Language/SELECT
statement. It's listed under See also near the bottom of the page.)

3) For full control over what goes into Excel, you need to use
Automation and (often) Excel's Range.CopyFromRecordset method, which can
paste the contents of a recordset into any specified location in a
workbook.

 

[Cecilia Reyes]

where can i read on Automation? i've heard of this but
havent found info

-----Original Message-----
Hi Gary,

1) DoCmd.TransferSpreadsheet cannot export to a specific range but can
export to a specific sheet in an existing workbook . Just pass the sheet
name as the Range argument, e.g.

DoCmd.TransferSpreadsheet acExport,

acSpreadsheetTypeExcel9, "Table",

"C:\folder\file.xls", -1, "SheetName"

2) Often it's preferable to use Access to summarise one's data. You can
access data in other databases by using linked tables or by using the
SQL IN clause in queries.

(For help on the latter, go to the contents page of Access help and look
for Microsoft Jet SQL Reference/Data Manipulation Language/SELECT
statement. It's listed under See also near the bottom of the page.)

3) For full control over what goes into Excel, you need to use
Automation and (often) Excel's Range.CopyFromRecordset method, which can
paste the contents of a recordset into any specified location in a
workbook.

I can easily export to a new excel spreadsheet. How do I
export to a certain "Sheet3" of an existing spreadsheet
copying over the existing "Sheet3" or deleting it and
creating another "Sheet3". I the end I plan to have
seven sheets that are exporting to from different
databases and the eight sheet will generate totals from
the other seven sheets. Please help. Thanks.

Gary

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi Cecilia,

The following articles should get you started:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

ACC: Sending the Current Record to Word 97 with Automation (Q131583)
http://support.microsoft.com/?id=131583

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box with
values from a Microsoft Excel worksheet

http://www.mvps.org/access/modules/mdl0043.htm
Opening a new Word document based on a template through Automation

There's also a detailed white paper and code samples including tutorials
available from Microsoft at
http://support.microsoft.com/?id=253235

where can i read on Automation? i've heard of this but
havent found info

-----Original Message-----
Hi Gary,

1) DoCmd.TransferSpreadsheet cannot export to a specific range but can
export to a specific sheet in an existing workbook . Just pass the sheet
name as the Range argument, e.g.

DoCmd.TransferSpreadsheet acExport,

acSpreadsheetTypeExcel9, "Table",

"C:\folder\file.xls", -1, "SheetName"

2) Often it's preferable to use Access to summarise one's data. You can
access data in other databases by using linked tables or by using the
SQL IN clause in queries.

(For help on the latter, go to the contents page of Access help and look
for Microsoft Jet SQL Reference/Data Manipulation Language/SELECT
statement. It's listed under See also near the bottom of the page.)

3) For full control over what goes into Excel, you need to use
Automation and (often) Excel's Range.CopyFromRecordset method, which can
paste the contents of a recordset into any specified location in a
workbook.

I can easily export to a new excel spreadsheet. How do I
export to a certain "Sheet3" of an existing spreadsheet
copying over the existing "Sheet3" or deleting it and
creating another "Sheet3". I the end I plan to have
seven sheets that are exporting to from different
databases and the eight sheet will generate totals from
the other seven sheets. Please help. Thanks.

Gary

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.