Hi,

I am creating an automated Querying System in Access and want to export 
the Queries to seperate Excel Worksheets in the same Spreadsheet.

I have created the Excel object in Access VB :

Set appxl = CreateObject("Excel.Application")   'MAKE THE APP OBJECT
    appxl.Visible = True                            'SHOW EXCEL
    appxl.Workbooks.Add                             'ADD A WORKBOOK
    Set oWb = appxl.ActiveWorkbook                  'MAKE WORKBOOK ACTIVE
    'ADD A WORKSHEET
    oWb.Worksheets.Add.Move after:= _
    oWb.Worksheets(oWb.Worksheets.Count)

    'ADD SOME SAMPLE FORMATTING TO ADDED SHEET
    oWb.Worksheets(oWb.Worksheets.Count).Cells(1, 1).Value = "This is the 
first cell"
    oWb.Worksheets(oWb.Worksheets.Count).Name = "Last Sheet"


How can I send queries to these worksheets???????

Thanks 

Tim Maher

Using ADODB:

where RS is an ADODB recordset

oWb.Worksheets("WorksheetName").Range("A1").Copyfromrecordset RS

|-----Original Message-----
|From: Tim Maher [mailto:tim.maher@s...]
|Sent: Tuesday, July 23, 2002 9:50 AM
|To: Access
|Subject: [access] Export from Access to Excel
|
|
|Hi,
|
|I am creating an automated Querying System in Access and want to export 
|the Queries to seperate Excel Worksheets in the same Spreadsheet.
|
|I have created the Excel object in Access VB :
|
|Set appxl = CreateObject("Excel.Application")   'MAKE THE APP OBJECT
|    appxl.Visible = True                            'SHOW EXCEL
|    appxl.Workbooks.Add                             'ADD A WORKBOOK
|    Set oWb = appxl.ActiveWorkbook                  'MAKE WORKBOOK ACTIVE
|    'ADD A WORKSHEET
|    oWb.Worksheets.Add.Move after:= _
|    oWb.Worksheets(oWb.Worksheets.Count)
|
|    'ADD SOME SAMPLE FORMATTING TO ADDED SHEET
|    oWb.Worksheets(oWb.Worksheets.Count).Cells(1, 1).Value = "This is the 
|first cell"
|    oWb.Worksheets(oWb.Worksheets.Count).Name = "Last Sheet"
|
|
|How can I send queries to these worksheets???????
|
|Thanks 
|
|Tim Maher
|

I had a similar sort of problem the other day (You might want to look at the thread 
"TransferSpreadsheet Problems")
http://p2p.wrox.com/view.asp?list=access&id=195325

I don't put any formatting in the Excel, but you could always put a bit of code into the Excel file 
rather than the export function to change that.

Not sure if this will solve your problem, but it should point you in the right direction

Steven

Cheers MAte!

Tim Maher
Information Officer
Swansea NHS Trust
Tel 01792 703661

>>> leoscott@c... 07/23/02 03:34pm >>>
Using ADODB:

where RS is an ADODB recordset

oWb.Worksheets("WorksheetName").Range("A1").Copyfromrecordset RS

|-----Original Message-----
|From: Tim Maher [mailto:tim.maher@s...] 
|Sent: Tuesday, July 23, 2002 9:50 AM
|To: Access
|Subject: [access] Export from Access to Excel
|
|
|Hi,
|
|I am creating an automated Querying System in Access and want to
export 
|the Queries to seperate Excel Worksheets in the same Spreadsheet.
|
|I have created the Excel object in Access VB :
|
|Set appxl = CreateObject("Excel.Application")   'MAKE THE APP OBJECT
|    appxl.Visible = True                            'SHOW EXCEL
|    appxl.Workbooks.Add                             'ADD A WORKBOOK
|    Set oWb = appxl.ActiveWorkbook                  'MAKE WORKBOOK
ACTIVE
|    'ADD A WORKSHEET
|    oWb.Worksheets.Add.Move after:= _
|    oWb.Worksheets(oWb.Worksheets.Count)
|
|    'ADD SOME SAMPLE FORMATTING TO ADDED SHEET
|    oWb.Worksheets(oWb.Worksheets.Count).Cells(1, 1).Value = "This is
the 
|first cell"
|    oWb.Worksheets(oWb.Worksheets.Count).Name = "Last Sheet"
|
|
|How can I send queries to these worksheets???????
|
|Thanks 
|
|Tim Maher
|

This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.