Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update or coping data into excel sheet.Help needed.

    Hi,
    I do have an access database which I use an asp page to add in/ update data into it.
    I am now creating an excel sheet to publish the last 25 data in line chart format thru web page. (I mean using IE to open the excel file). My question are:-
    a) how can I auto copy the latest 25 data from my db into excel sheets.( I am now doing it manualy).

    b) the excel file is always open in anothers window, I think before the transfer it is needed to close the excel sheet.Correct me if I am wrong

    Pls help me on this:-
    in summary my excel filename is "chart.xls", sheet name "spc"
    I can list out the last 25 data as below:-
    Code:
    sql="select top 25 Avg, Range, from tblFiles where mcno = 2  Order by date DESC"
    Every time when I update my db I need to transfer the last 25 "avg" and "range" data from my db1.mdb into column a10 to O10 for avg and a11 to o11 for range data. After that save the excel file & then reopen it with the new data page.

    I have never deal with excel before, hope that some one can help & guide me thru this.tq.

  • #2
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All you ened to do is in the asp page, change the contenttype into something that is not reconized by browsers, and then set the filename to junk.xls, and it will ask them if they want to open it as an excel file.

    Excel will handle the transition from HTML table to Excel table!

    ASP:
    Code:
    Response.ContentType = "application/x-download"		
    Response.AddHeader "content-disposition","attachment; filename=" & fileName & ".xls"
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by SpirtOfGrandeur
    All you ened to do is in the asp page, change the contenttype into something that is not reconized by browsers, and then set the filename to junk.xls, and it will ask them if they want to open it as an excel file.

    Excel will handle the transition from HTML table to Excel table!

    ASP:
    Code:
    Response.ContentType = "application/x-download"		
    Response.AddHeader "content-disposition","attachment; filename=" & fileName & ".xls"
    Hi tks for the fast response & help.

    I already have the excel file which i am now using it to display my data in line chart format.

    what I need is to update the column automatically whenever there is new data being update into access db. This is b'cos I only want to show the latest 25 data to all the user.

    How can I copy & paste the data into my excel file thru asp?

    tks

  • #4
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You cannot read a file on the user’s pc and update it via ASP.

    All you can do is recreate the sheet every time which is relatively easy...
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #5
    New Coder
    Join Date
    Aug 2005
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi SpirtOfGrandeur,
    I maybe not explain it clear. The excel sheet that display are save under server same directory with all my asp page, I display the excel file to user if they click on graph link. I want to automatically copy the latest 25 data from my access db into excel sheet everytime user update the db.
    Hope that you have a clearer picture on my problem.tq


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •