Export to Range from Access with TransferSpreadsheet
The Range argument only applies when importing.
Sorry, that's not correct. Let this be a warning to others: Usenet contains
lots of bad advice.
Private Const P As Long = 80
r = 10 'starting row
cc = 5 'column count
rc = 24 'row count
For b = 1 To bytNumberOfTables
strRange = strSheetName & "!" & Chr(P) & r & ":" & Chr(P + cc) & rc
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel8, _
TableName:="tblExcelData" & b, _
Filename:=strXlsPath, _
HasFieldNames:=True, _
Range:=strRange
r = r + 50
rc = rc + 50
Next
This works like a charm for exporting multiple tables (tblExcelData1,
tblExcelData2, etc.) from Access into the same Excel worksheet, separated by
50 rows. The only catch is that the worksheet must already exist.
|