How to specify Range when exporting to Excel with JET?

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.

re: How to specify Range when exporting to Excel with JET?

On Sat, 11 Jun 2005 11:27:00 GMT, deko wrote:
[color=blue]
> 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.[/color]

VBA help is quite clear on this:

Range Optional Variant. 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.<<<

What I would do is transfer each group of data into a separate
worksheet, then using code from within Excel, cut and paste into
whatever cells you want on the worksheet you want.
You can use the Excel Record New Macro once to get the code needed,
then modify it if necessary.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: How to specify Range when exporting to Excel with JET?

> VBA help is quite clear on this:

Indeed it is:

http://msdn.microsoft.com/library/de...HV05186520.asp

The funny thing is, I've been testing for a while now and it seems to be
working fine. Granted, my development workstation has all the latest and
greatest software. It will be interesting to see what happens on
older/unpatched systems.

This is the first "reverse bug" (where something works that's not supposed
to) that I've ever discovered. Dare I include this code in a production
release? hmmm......
[color=blue]
> What I would do is transfer each group of data into a separate
> worksheet, then using code from within Excel, cut and paste into
> whatever cells you want on the worksheet you want.
> You can use the Excel Record New Macro once to get the code needed,
> then modify it if necessary.[/color]

That makes sense. If I can get my unsupported code to break, that's
probably what I'll do.

Below is the actual code I'm using. I create the worksheet with JET by
inserting a one-cell string in A1.

Private Const P As Long = 80

Do While Not rstWorksheets.EOF

'TransferSpreadsheet requires the worksheet to already exist
strSql = "SELECT """" AS " & strTitle & " INTO " & _
"[Excel 8.0;Database=" & strXlsPath & "].[" & _
strSheetNameID & "]"
db.Execute (strSql), dbFailOnError
cc = bytDie + 2 'column count
fr = 7 'first row
'increment row count by 1 to include header row
'(all tables will have the same number of rows)
rc = DCount("DateTime", "tblExcelData1") + 1
lr1 = rc + 6 'initial last row
'loop through anywhere from 1 to 15 or so tables
For b = 1 To bytSite
If b = 1 Then lr = lr1
strRange = Chr(P) & fr & ":" & Chr(P + cc) & lr
Debug.Print strRange
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel8, _
TableName:="tblExcelData" & b, _
Filename:=strXlsPath, _
HasFieldNames:=True, _
Range:=strSheetNameID & "!" & strRange
If rc < 36 Then
fr = fr + 42
Else
fr = lr + 7
End If
If rc < 36 Then
lr = lr + 42
Else
lr = lr + 42 + (rc - 36)
End If
'save ranges for creating charts later
Call basHandler.Logger(strSheetNameID, strRange, _
strParamName, strSysId, lngPid, , 120)
Next

Loop