Export Query to Specific Sheet and Rows/Columns

[chewbears]

Private Sub Command3_Click()
 
    Dim reportName As String
    Dim theFilePath As String
 
''    On Error Resume Next
    Select Case Me.Frame4.Value
        Case 1
            reportName = "Query1"
        Case 2
            reportName = "Query2"
    End Select
 
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"
    theFilePath = theFilePath & reportName & ".xlsx"
 
    DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True
    MsgBox "Check your Desktop."
 
End Sub

Running the above code on a button. Is it possible to add options to the export to tell it a particular sheet and/or particular rows and columns.

Thanks,

[alansidman]

Read this link about saving to a particular range in a particular spreadsheet. This was written for an earlier version of Access so be sure not to change the Transfer spreadsheet section of the docmd line but only the end of the code line.
http://accessblog.net/2006/07/export...cel-range.html
Alan

[chewbears]

Read this link about saving to a particular range in a particular spreadsheet. This was written for an earlier version of Access so be sure not to change the Transfer spreadsheet section of the docmd line but only the end of the code line.
http://accessblog.net/2006/07/export...cel-range.html
Alan

Thank you Alan,

I had beed playing with the [Range] portion of the command but specifically could not get the Sheet+range to work. Perhaps it is how I am writing it. To go off of your blog link if I am creating an export to the "myname" sheet, how can I further direct it to exporting to a specific range of cells. Simply myname$A3:BA3 would not work. I saw in a response below that

"Was just able to export to Excel from Access 2007 to specific range using:
Call Application.DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel8, Access_Table, FullPathToExcelWorkbook.xls, False, Sheet1$A3:BA3)"

I am not having the same luck

[alansidman]

What does your docmd line look like when you try to export to a specific range and spreadsheet.

[chewbears]

    DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True, myname$A2:B5

[chewbears]

Any Ideas?

[alansidman]

I can't get it to work either. I'm hoping someone who has used this concept will jump in here.

[chewbears]

Thanks for the reply Alan. Been trying all sorts of things, but always getting the same error ; P