Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Using worksheet data in a Web page (2)

    Details of my golf club's fixtures and bookings are listed in a worksheet on a daily basis for a calendar year and frequently edited for changes and new bookings. This work is done in the club's office. The current worksheet is emailed to me each month. As a result of my <post#=668,786>post 668,786</post#> the assistance I received has been helpful in taking the task further to a solution but I am not there yet; one major hurdle needs clearing!

    The pages have been created in FrontPage 2003 by saving the worksheet as a .csv file, importing that file into FrontPage, opening it in Notepad in FrontPage (open with), copying and pasting into a new page and using Table | Convert | Text to Table to create the table of data. I have then formatted the table using CSS. So far so good. But now I cannot see a way to update the data other than manually! Ideally I would want to delete the existing data from the table and copy and paste the newly issued data into the table so not disturbing the formatting similar to the copying and pasting of data in Excel.

    Any constructive suggestions will be gratefully received.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using worksheet data in a Web page (2)

    Hi Malcolm

    By far the easiest way is to create an active server page

    I created a page at http://www.magicforest.co.uk/jezza.asp as a test to get a large amount of data outputted to my website, the code for the page is below:

    <pre><%@ Language= "VBScript" %>
    <html>
    <head>
    <title></title>
    <link href="css/main.css" rel="stylesheet" type="text/css">
    </head>
    <body>

    <h2>Magical Numbers</h2>

    <%


    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DBQ=" & Server.MapPath("numbers.xls") & ";" & "DRIVER={Microsoft Excel Driver (*.xls)};"

    strSELECT = "SELECT * from `myData`"

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSELECT, objConn

    objRS.MoveFirst


    Response.Write "<h4>The magic of Numbers in the Magic Forest</h4>"

    Response.Write "<table><tr>"

    For i = 0 to (objRS.Fields.Count - 1)

    Response.Write "<td><h3>" & objRS(i).Name & "</h3></td>"
    Next


    While Not objRS.EOF

    Response.Write "</tr><tr>"

    'For each column in the current row...
    For i = 0 to (objRS.Fields.Count - 1)
    'write out the data in the field.
    %><td><h3><%=objRS(i)%></h3></td><%
    Next

    'Move to the next row.
    objRS.MoveNext

    Wend

    Response.Write "</tr></table>"

    'Close the Connection.
    objConn.Close
    %>


    </body>
    </html>
    </pre>


    I have an Excel file in the root folder called number.xls and the data that is held in a flat file (a worksheet) has been named myData. The data loops through all the way through the recordset and displays it between <h3> and <td> tags

    Have a look at this <post:=675,224>post 675,224</post:> where I solved the problem of the connectivity. I hope this is a start?
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Using worksheet data in a Web page (2)

    Jerry,

    ASP? Thank you but your suggestion takes me into the unknown!

    For my monthly diaries would I need 12 pages with their files named jan.asp, feb.asp, mar.asp, apr.asp and so on to dec.asp? Would each file contain a table whose cells would be populated from a updated worksheet by an ASP routine such as the example in your reply?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using worksheet data in a Web page (2)

    Hi Malcolm

    No one sheet will do, I just did a test in my localhost and you just have to create 12 named ranges. I just created 2 Jan and Feb but there is plenty of space to create the whole year.

    The formatting may be a bit of a challenge but it will be worth the work as all you have to do is write in the Excel workbook, save it and the page will pick it up

    <pre><%@ Language= "VBScript" %>
    <html>
    <head>
    <title></title>
    <link href="css/main.css" rel="stylesheet" type="text/css">
    </head>
    <body>

    <h2>Magical Numbers</h2>

    <%

    'strDriver = "Driver={Microsoft Excel Driver (*.xls)};DBQ=numbers.xls;"
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DBQ=" & Server.MapPath("numbers.xls") & ";" & "DRIVER={Microsoft Excel Driver (*.xls)};"
    'objConn.Open strDriver


    strSELECT = "SELECT * from `Jan`"

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSELECT, objConn

    objRS.MoveFirst


    Response.Write "<h4>Jan</h4>"

    Response.Write "<table><tr>"

    For i = 0 to (objRS.Fields.Count - 1)

    Response.Write "<td><h3>" & objRS(i).Name & "</h3></td>"
    Next


    While Not objRS.EOF

    Response.Write "</tr><tr>"

    'For each column in the current row...
    For i = 0 to (objRS.Fields.Count - 1)
    'write out the data in the field.
    %><td><h3><%=objRS(i)%></h3></td><%
    Next

    'Move to the next row.
    objRS.MoveNext

    Wend

    Response.Write "</tr></table>"

    strSELECT = "SELECT * from `Jan`"

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open strSELECT, objConn

    objRS.MoveFirst


    Response.Write "<h4>Feb</h4>"

    Response.Write "<table><tr>"

    For i = 0 to (objRS.Fields.Count - 1)

    Response.Write "<td><h3>" & objRS(i).Name & "</h3></td>"
    Next


    While Not objRS.EOF

    Response.Write "</tr><tr>"

    'For each column in the current row...
    For i = 0 to (objRS.Fields.Count - 1)
    'write out the data in the field.
    %><td><h3><%=objRS(i)%></h3></td><%
    Next

    'Move to the next row.
    objRS.MoveNext

    Wend

    Response.Write "</tr></table>"

    'Close the Connection.
    objConn.Close
    %>


    </body>
    </html>
    </pre>

    Jerry

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Using worksheet data in a Web page (2)

    Jerry,

    What I am aiming for can be seen on my development site at the url: http://www.mewalker.co.uk/membersarea. Please have a look and let me know if what you are telling me about one page is consistent with what you see. In the left hand vertical navigation click on the Fixtures and Bookings link and initially go to the January page as this is the nearest to `finished'.

    If I have one page then in the rendered HTML I think it would have at least 7000 lines!

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Using worksheet data in a Web page (2)

    > If I have one page then in the rendered HTML I think it would have at least 7000 lines!

    The rendered page should be no longer than the ones you create manually. It's just of method of reading the details from a data file given a parameter (in this case, a named range) so that you never need to create another page manually.

Posting Permissions

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