The Range argument only applies when importing.
Perhaps you could dump each copy of the table to a different sheet, e.g.:

Dim i As Integer
Dim bytNumOfTables As Integer
Dim strTable
bytNumOfTables = 2
i = iStart
strTable = "tblExcelData"

For i = 1 To bytNumOfTables
CurrentDb.TableDefs(strTable).Name = strTable & i
db.Execute ("DROP TABLE tblExcelData"), dbFailOnError
db.Execute ("qryMakeNewTable"), dbFailOnError
db.Execute ("qryPopulateExcelData"), dbFailOnError
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel9, _
TableName:=strTable & i, _
FileName:="C:\PathToWorkbook", _
HasFieldNames:=True
CurrentDb.TableDefs(strTable & i).Name = strTable
Next


deko wrote:
I need to export a series of different Access tables into a single Excel
worksheet with
DoCmd.TransferSpreadsheet.

The problem is positioning each table of data properly in the worksheet:
there needs to be 40 rows between each table.

I'm creating each table on the fly, then dumping it out to Excel like this:

i = 10
For i = 1 To bytNumOfTables
db.Execute ("DROP TABLE tblExcelData"), dbFailOnError
db.Execute ("qryMakeNewTable"), dbFailOnError
db.Execute ("qryPopulateExcelData"), dbFailOnError
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel5, _
TableName:="tblExcelData", _
FileName:="C:\PathToWorkbook", _
HasFieldNames:=True, _
Range:="P" & i
i = i + 40
Next

So I'm expecting a contiguous block of rows and columns (about 15 rows and
20 columns) starting at P10, then another at P50, another at P90, and so on.
To the left of each contiguous block will be a graph of the data.

Am I specifying the Range parameter correctly?

Why isn't

Range:="P" & i

working?

Is there a better way to do this?

Thanks in advance.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html