transfer to a particular sheet in workbook

[Joe C]

How would I adjust this command to transfer to a particular sheet of a work book. Is it possible.

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "qtable1", savename, True

 

[bill]

not sure I understand.

Transfer an existing spreadsheet?
To where?

If you're trying to move an existing sheet from onw WB to another (from Access), you'll need to expose the Excel Object model in your Access VBA code and do it basically like you would from Excel.

 

[Corticus]

Hi,

This is going to be tricky. The
'DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "YourQuery/Table

automatically goes to the first sheet, as well as
DoCmd.OutputTo acReport, "MyReport", acFormatXLS

The only option I can think of, is to leave your first page blank, and cut and paste it to another sheet from Access.

Sub export() 

'name spreadsheet for object reference 
Dim ExcelSheet As Object 

'name string vars for dir and spreadsheet file name 
Dim savename As String 

'Add reference to Excel library 
Call XLLibrary 

'get path info 
savename = inputbox("Where would you like to save?") 

'transfer qryXL to file 'savename', closes file 
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "YourQuery/Table", Savename, True 

'opens new file 
Workbooks.Open Filename:=savename 

'set object 'savename'(excel spreadsheet) for action 
Set ExcelSheet = Workbooks.Application.ActiveWorkbook 

'all excel VBA
With ExcelSheet 
    .Application.Visible = True
    .Sheets(1).Cells.Copy
    .Sheets(2).Paste
    .Save 
    .Quit 
End With 

End Sub 
_________________________________________
Sub XLLibrary() 

'Add reference to Excel library 

On Error Resume Next 
Application.References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 2, 3 

End Sub

Of course, if you could leave the first page blank, you probably wouldn't need to export to another one, so my answer may be of little help.

You could try importing into Excel instead of exporting, perhaps. If this is a possibility, let us know.

edit:
This:
'DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "YourQuery/Table
can create a new spreadsheet. You could then modify the above code to copy the page from this new spreadsheet, and paste it into your existing one, and then delete the tesmp worksheet.

HTH,