Transferspreadsheet - simple question

[Kara625]

I am new to VBA coding, but I read through the MSAccess instructions and a bunch of forum postings and I still can't figure out my question:

Could someone please lay it out for me how to use TransferSpreadsheet or OutputTo to send the output from two Access queries into two different tabs in one Excel file?

I have found bits and pieces of the answer all over the internet and I can't figure out how to put them together! :) Thanks.

Kara

[PEB]

I am new to VBA coding, but I read through the MSAccess instructions and a bunch of forum postings and I still can't figure out my question:

Could someone please lay it out for me how to use TransferSpreadsheet or OutputTo to send the output from two Access queries into two different tabs in one Excel file?

I have found bits and pieces of the answer all over the internet and I can't figure out how to put them together! :) Thanks.

Kara

Hi,

Maybe your question isn't so easy

This command transfers the data but on only one sheet!

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Employees","C:\my_file.xls", True, ""


For more sheets you need to consult so you have to refer to:

Working Across Applications

In the Access Basic help...

Visual Basic can create new objects and retrieve existing objects from many Microsoft applications. Other applications may also provide objects that you can create using Visual Basic. See the application's documentation for more information.

To create an new object or get an existing object from another application, use the CreateObject function or GetObject function:

' Start Microsoft Excel and create a new Worksheet object.
Set ExcelWorksheet = CreateObject("Excel.Sheet")

' Start Microsoft Excel and open an existing Worksheet object.
Set ExcelWorksheet = GetObject("SHEET1.XLS")

' Start Microsoft Word.
Set WordBasic = CreateObject("Word.Basic")

Most applications provide an Exit or Quit method that closes the application whether or not it is visible. For more information on the objects, methods, and properties an application provides, see the application's documentation.

Some applications allow you to use the New keyword to create an object of any class that exists in its type library. For example:

Dim X As New Field

In this case, Field is an example of a class in the data access type library. A new instance of a Field object is created using this syntax. Refer to the application's documentation for information about which object classes can be created in this way.


Hope that's given some directions

:)

[MMcCarthy]

If you use DoCmd.TransferSpreadsheet to send two different objects to an excel file. They should default to two different worksheets with the worksheets automatically given the object names.

Try it and see.

I am new to VBA coding, but I read through the MSAccess instructions and a bunch of forum postings and I still can't figure out my question:

Could someone please lay it out for me how to use TransferSpreadsheet or OutputTo to send the output from two Access queries into two different tabs in one Excel file?

I have found bits and pieces of the answer all over the internet and I can't figure out how to put them together! :) Thanks.

Kara

[PEB]

If you use DoCmd.TransferSpreadsheet to send two different objects to an excel file. They should default to two different worksheets with the worksheets automatically given the object names.

Try it and see.

In the Access Basic help i've seen that if you specify the sheet or the range there is no export because of fault... Microsoft wants to be blank there as range!
;(