Access export to specific excel cells

You can't define a range when exporting. See this bit of information from http://msdn.microsoft.com/en-us/library/office/ff844793(v=office.15).aspx, regarding the Range argument:

A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

The only way I know of to export to specific cells in Excel is to use automation.

can you point me in the direction of automation - an example out on the net or something  that I can see how its done?

Here's a good starting point:

http://support.microsoft.com/kb/219151

Note that this uses "Early Binding", which means you must set a reference to Excel. If you plan on distributing the app to others, this may cause issues, so often we'll develop with Early Binding (i.e. with the Reference to Excel set) and then switch over to "Late Binding". Basically, Late Binding means we remove the reference, and change the way we work with the library. Instead of writing this:

Dim xl As Excel.Application

We would do this:

Dim xl As Object
Set xl = CreateObject("Excel.Application")

This would use whatever version of Excel is on the end user machine, and would not tie your application to a specific version. There are some downsides to this method - for example, you must be sure to NOT use constants and such that are specific to the host library, you don't get Intellisense when working in VBA, etc - but overall the pluses outweigh the minuses.

OK I kinda switched gears
it hit me last nite that I could just build a macro in excel
and then open and run the macro to do my bidding.
However I'm having a problem with getting the macro to run.
Here's the code I'm using in access:
It opens the file and the data is there and I can manually run the macro and it all works
but it wont run the macro from access any thoughts?

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim fd As Long
Dim DirName As String
 
DoCmd.SetWarnings False
DoCmd.Hourglass True
 
fd = Format(Date, "yyyymmdd")
 
sTemplateA = CurrentProject.Path & "\Templates\Benefit Config Acct Set Up Template.xls"
sOutputa = CurrentProject.Path & "\FileOut\Benefit Config Acct Set Up " & fd & ".xls"
If Dir(sOutputa) <> "" Then Kill sOutputa
FileCopy sTemplateA, sOutputa
 
DoCmd.TransferSpreadsheet acExport, 8, "Structure", sOutputa, False
DoCmd.TransferSpreadsheet acExport, 8, "RX_Benefits", sOutputa, False
DoCmd.TransferSpreadsheet acExport, 8, "Residence States for PDP", sOutputa, False
 
Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
    Set xlWB = .Workbooks.Open(sOutputa)
            xlWB.Run "Transfer_Data"     (This is the macro)
    End With

I don't have any idea why it wouldn't run. You'd have to ask that of the Excel experts ...

Thank you works great