Creating Excel file + inserting values

Discussion in 'ASP General' started by Bertrand, Oct 1, 2003.

  1. Bertrand

    Bertrand Guest

    Hello,

    I am trying to archieve the following:

    - copy an excel file present on the server
    - insert values into named ranges of the copy

    I am using Excel97 on my PC, the server does not have
    Excel installed on it so I can't create the object
    Excel.Application

    The server runs Microsoft-IIS/4.0

    Although things seem to work, I have a couple of issues:
    - when I insert a value into the new copy, the named range
    is deleted
    - the value is inserted into the worksheet in the cell
    BELOW the named cell.
    - I don't know how to refer to a cell by its address
    instead of a named range.

    I am using the following code:
    <%

    Dim sSourceXLS
    Dim sDestXLS

    sSourceXLS = Server.MapPath(".") & "\test2.xls"
    sDestXLS = Server.MapPath(".") & "\AutoCRF2.xls"

    'Copy the source workbook file (the "template") to the
    destination filename
    Dim fso
    Set fso = Server.CreateObject
    ("Scripting.FileSystemObject")
    fso.GetFile(sSourceXLS).Copy sDestXLS
    Set fso = Nothing

    'Open the ADO connection to the destination Excel
    workbook
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDestXLS & ";" & _
    "Extended Properties=""Excel 8.0;HDR=NO;"""


    'Add values to individual cells
    oConn.Execute "Insert into FirstName Values ('Nancy')"
    oConn.Execute "Insert into Surname Values ('Leroy')"
    oConn.Execute "Insert into SalaryCode Values
    ('GM0001')"
    oConn.Execute "Insert into DateLastPayed Values
    ('05/09/2003')"
    oConn.Execute "Insert into Address Values ('30/1
    Pilrig House Close')"
    oConn.Execute "Insert into Telno Values ('0131-
    5663452')"


    oConn.Close

    %>

    Does someone know why the cell below the named cell is
    updated?
    why is the name deleted?
    and/or
    How to refer to a range by its address instead of its name.

    Any help would be greatly appreciated,

    Thanks,

    kindest regards,

    Bertrand


    I am using
    Bertrand, Oct 1, 2003
    #1
    1. Advertising

  2. Bertrand

    Ray at Guest

    Hi Bertrand,

    When using named ranges, you have to name the cell that is above the one
    that you want to insert into. Think of the named range as a column header
    in a table design or something.

    Ray at work


    "Bertrand" <> wrote in message
    news:051301c387f4$a9647950$...
    > Set oConn = Server.CreateObject("ADODB.Connection")
    > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sDestXLS & ";" & _
    > "Extended Properties=""Excel 8.0;HDR=NO;"""
    >
    > Does someone know why the cell below the named cell is
    > updated?
    Ray at, Oct 1, 2003
    #2
    1. Advertising

  3. Bertrand

    Ray at Guest

    I don't know of any way to say like "update [sheet1$] set [f1]='some value'
    where ADDRESS='$a$1'"

    And it seems that all the ADO samples out there for Excel concentrate mostly
    on SELECTS, INSERTs, or UPDATEs that don't specify any range info.

    Maybe someone else knows more?

    Ray at work

    "Bertrand" <> wrote in message
    news:021801c38834$1e6861a0$...

    > Do you also know if it possible (and if so how) to target
    > a cell by refering to it by its address instead of its
    > name?
    >
    > thanks,
    >
    > Bertrand
    Ray at, Oct 1, 2003
    #3
    1. Advertising

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

    error when inserting unique values

    Muhammad Usman, Oct 16, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    1,096
    Srini
    Oct 16, 2003
  2. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,364
    Carl Prothman [MVP]
    Jan 12, 2005
  3. Replies:
    1
    Views:
    1,773
    extentechjohn
    Nov 21, 2006
  4. Replies:
    0
    Views:
    315
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,481
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page