export to Excel

[david epsom dot com dot au]

I'm trying to export to a position (A10) in a new spread sheet, rather than
using a named range.


I'm using a make table query. This doesn't work:

sSQL = "SELECT tblYC_YieldCurve.strPricingCode AS Yield_Code
sSQL = sSQL & " into [EXCEL 8.0;database=" & sfile & "].[A10] "
sSQL = sSQL & " FROM tblYC_PointData "
CodeDb.Execute sSQL, DAO.dbSeeChanges

I get a page named _A10, not data starting a A10.

[A10:G50] doesn't work either.

Any suggestions?

(david)

 

[Jamie Collins]

I'm trying to export to a position (A10) in a new spread sheet, rather than
using a named range.

I'm using a make table query. This doesn't work:

sSQL = "SELECT tblYC_YieldCurve.strPricingCode AS Yield_Code
sSQL = sSQL & " into [EXCEL 8.0;database=" & sfile & "].[A10] "
sSQL = sSQL & " FROM tblYC_PointData "
CodeDb.Execute sSQL, DAO.dbSeeChanges

I get a page named _A10, not data starting a A10.

[A10:G50] doesn't work either.

You must specify a valid defined Name ('named range') for the
SELECT..INTO syntax. So, no, specifying a range address is not
possible in this scenario.

Here is my suggestion for achieving the results you require (I'm
guessing the data type for your Yield_Code column is text):

Execute this to create the new workbook, worksheet and the inevitable
defined Name:

CREATE TABLE
[Excel 8.0;HDR=Yes;Database=C:\david.xls;].Sheet1
(FakeHeader VARCHAR(15))
;

Then this *eight* times:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\david.xls;].Sheet1
(FakeHeader) VALUES ('delete_me')
;

Then this to insert the correct header in the correct place:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\david.xls;].Sheet1
(FakeHeader) VALUES ('Yield_Code')
;

Then this to insert the data in the required location:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\david.xls;].Sheet1
(FakeHeader)
SELECT
tblYC_YieldCurve.strPricingCode AS FakeHeader
FROM
tblYC_PointData
;

Finally, this to erase the unwanted rows:

UPDATE
[Excel 8.0;HDR=No;Database=C:\david.xls;].Sheet1
SET
F1=''
WHERE
F1='FakeHeader' OR F1='delete_me'
;

Jamie.

--