export to Excel

Discussion in 'Microsoft Access External Data' started by david epsom dot com dot au, Jul 15, 2004.

  1. 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)
     
    david epsom dot com dot au, Jul 15, 2004
    #1
    1. Advertisements

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

    --
     
    Jamie Collins, Jul 15, 2004
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Nurick

    Re: can't export from access to excel - no excel option available

    John Nurick, Aug 12, 2003, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    581
    John Nurick
    Aug 12, 2003
  2. Ray

    Export to TXT - Export Specification & Headers

    Ray, Jan 11, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    563
    Ken Snell
    Jan 11, 2004
  3. EXPORT EXPORT EXPORT

    , May 11, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    578
    Douglas J. Steele
    May 12, 2004
  4. trist123

    Export from 2007 using Export Specification.

    trist123, Mar 13, 2008, in forum: Microsoft Access External Data
    Replies:
    2
    Views:
    386
    trist123
    Mar 13, 2008
  5. efandango

    Create an export file using user generated title and export data t

    efandango, Oct 4, 2008, in forum: Microsoft Access External Data
    Replies:
    6
    Views:
    553
    Douglas J. Steele
    Oct 5, 2008
Loading...

Share This Page