PC Review


Reply
Thread Tools Rate Thread

export to Excel

 
 
david epsom dot com dot au
Guest
Posts: n/a
 
      15th Jul 2004
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)


 
Reply With Quote
 
 
 
 
Jamie Collins
Guest
Posts: n/a
 
      15th Jul 2004
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote ...

> 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.

--
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Excel tuncating leading zeros while export to excel from da RHBKV Microsoft Excel Setup 1 15th Jul 2009 01:48 PM
Export contacts to Excel - No export from folders show up... =?Utf-8?B?Q2hyaXN0b3BoZXI=?= Microsoft Outlook Contacts 2 19th Apr 2005 10:41 PM
Export contacts to Excel - No export from folders show up... =?Utf-8?B?Q2hyaXN0b3BoZXI=?= Microsoft Outlook Contacts 0 19th Apr 2005 04:11 PM
EXPORT EXPORT EXPORT jubejoie@aol.com Microsoft Access External Data 1 12th May 2004 12:05 AM
Outlook 97 - Journal - File - Import & export - Export to a file - Micros Excel Sam Microsoft Outlook Discussion 0 25th Jul 2003 01:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:44 PM.