By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

How to specify Range when exporting to Excel with JET?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sat, 11 Jun 2005 11:27:00 GMT, deko wrote:
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.


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.
Nov 13 '05 #2

P: n/a
> 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......
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.


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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.