Capturing Information using a Ms Excel web page

  • I know an excel spreadsheet can be saved as an interactive web page and published on the web.


    Is it possible I capture infornation using this published web page?


    In other words I want to users to input information in the webpage and save it.


    Thank you in advance

  • Hi Rennie,


    Very interesting and identical to the problem i am facing currently.


    I could not do it, and tried to do some R&D on that.


    The fact that we see a htm page from excel, is that the html code is internally created from excel and saved as htm.
    If there are more than one sheets, multiple htm pages are created inside a folder and the main htm just has a link to all other pages. Thus, the features of excel are completely lost.


    That is my own experience, and if someone has come over it, i will be the first person (may be second after Renni
    :coolwink:) interested in it.


    But thought, this will throw some more light on this issue

  • untested, so I'm guessing..... but, is it possible to also make the workbook shareable before publishable via HTML ?


    HMTL will let you pop it on the web and shareable allows multi-user on a network..... can you do both ?


    :eureka:
    :question:

  • Good to see there is some interest on this topic.


    I have tried sharing a workbook and then publishing it. I am still not effecting the desired behaviour.


    However, there is a longwinded way of doing this (I believe - worked for me once).


    The users will need to download the file work on it and save it on the webserver location. In doing this, they will have to remember to save the workbook/sheet with the same name and also to select the appropriate options to save it as an interactive web page.


    In short this would require quite a bit of training and users having publishing access to the webserver - needless to say this is much more difficult than updating and saving.


    The other issue is once the information is captured it can not be (I could not do it) consolidated the normal way using links etc.

  • I came across a way of doing what you want using ADO, some time ago I beleive I still have the code on my machine at home. If you care to leave an e-mail, if I come across it I will send it to. I will also post here for any future use.


    Bruce

  • Have not forgotten, I have been moving for the past few days, and I am just getting settled in. I will be looking for it, over the next week.


    Bruce

  • This was one file, I am still looking for another one that uses VBScript. I am not sure if this is what you are looking for, but you would be able to tweak this perhaps for your needs. When I come across the other one, I will post as well. This file comes from vbusers.com


    Bruce



    The following routine update the contents of an Excel Range using ADO and the JET OLEDB driver. An example can be found at the bottom of the post.


    Option Explicit



    'Purpose : Updates the contents of an Excel Spreadsheet using ADO
    'Inputs : sWorkbookPath The path of the workbook to update the range contents of.
    ' sRange The range name or range reference to update (eg. "A1:A20" or "MyRangeName")
    ' avNewValues A 2d zero based array of values to update the range with.
    ' [sWorkSheetName] The name of the worksheet to update. Only required in not using
    ' a range name for the sRange variable.
    'Outputs : Returns zero on success, else returns an error number.
    'Author : Andrew Baker
    'Date : 1/Aug/2001
    'Notes : Requires a reference to Microsoft ActiveX Data Objects 2.1 or greater.
    ' Uses the OLE DB Provider for Jet.


    Function ExcelRangeUpdate(sWorkbookPath As String, sRange As String, avNewValues As Variant, Optional sWorkSheetName As String = "") As Boolean
    Dim oConn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim oRS As ADODB.Recordset
    Dim lThisRow As Long, lThisCol As Long, bAddedRow As Boolean

    On Error GoTo ErrFailed
    'Open a connection to the Excel spreadsheet
    Set oConn = New ADODB.Connection
    '"HDR=Yes" means that there is a header row in the cell range (or named ranged), so the provider will NOT include the first row (of the selection) into the recordset.
    'If "HDR=No", then the provider will include the first row (of the cell range or named ranged) into the recordset.
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbookPath & ";Extended Properties=""Excel 8.0;HDR=No;"";"

    'Create a command object and set its ActiveConnection
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = oConn
    oCmd.CommandText = "SELECT * from `" & sWorkSheetName & "$" & sRange & "`"

    'Open a recordset containing the worksheet data.
    Set oRS = New ADODB.Recordset
    oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

    'Update the values in the recordset
    For lThisRow = 0 To UBound(avNewValues, 2)
    For lThisCol = 0 To UBound(avNewValues, 1)
    'Note, you will get a type mismatch if the range
    'already contains a value of a different type. eg.
    'updating a numeric cell with a string value will
    'give you a type mismatch. Get round this be using
    'the .Delete and .Add methods to add a new blank
    'row/cell
    oRS(lThisCol).Value = avNewValues(lThisCol, lThisRow)
    If bAddedRow Then
    oRS.Update
    End If
    Next
    oRS.MoveNext
    If oRS.EOF Then
    'The query only returns rows which have existing values or have
    'values after them. Must call AddNew for all other empty cells.
    oRS.AddNew
    bAddedRow = True
    End If
    Next

    If bAddedRow Then
    oRS.Update
    End If
    'Close the connection
    oRS.Close
    oConn.Close
    Set oRS = Nothing
    Set oCmd = Nothing
    Set oConn = Nothing

    Exit Function


    ErrFailed:
    'Failed
    Debug.Print "ExcelRangeUpdate Error: " & Err.Description
    Set oRS = Nothing
    Set oCmd = Nothing
    Set oConn = Nothing
    ExcelRangeUpdate = False
    End Function



    'Demonstration routine
    Sub Test()
    Dim avValues As Variant, lThisRow As Long
    ReDim avValues(0 To 0, 0 To 19)
    For lThisRow = 0 To 19
    avValues(0, lThisRow) = "Cell " & lThisRow + 1
    Next
    'Note you will need to save a blank workbook in "C:\" called "Test.xls"
    ExcelRangeUpdate "C:\test.xls", "Sheet1", "A1:A20", avValues
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!