I use a complied query to export to Excel like this:
SELECT * INTO [Excel 8.0;Database=C:\MyExcelWorkbook.XLS].[Sheet1]
FROM tblExcelData;
But I have a situation where I need to export several tables into the same
worksheet. The idea is to have each contiguous block of data on the
worksheet separated by 50 or so rows so a graph can be inserted between each
set of data.
The problem with this:
lngA = 64
r = 10
cc = 12 +16
For i = 1 To whatever
strRange = strSheetName & "!" & Chr(lngA + 16) & _
r & ":" & Chr(lngA + cc) & rc
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel8, _
TableName:="tblExcelData" & i, _
FileName:="C:\MyExcelWorkbook.xls", _
HasFieldNames:=True, _
Range:=strRange
r = r + 50
rc = rc + 50
Next
is that the worksheet must already exist in the workbook. Otherwise
DoCmd.TransferSpreadsheet fails.
I've tried this:
SELECT * INTO [Excel
8.0;Database=C:\MyExcelWorkbook.XLS].[Sheet1]!P10:U24
FROM tblExcelData;
but no luck.
Can I specify a range when exporting to Excel with JET? Is there a way to
get TransferSpreadsheet to create the worksheet if it does not already
exist?
Thanks in advance.