[tutorial] webbased guestbook with Excel as database-VBForums
Results 1 to 2 of 2

Thread: [tutorial] webbased guestbook with Excel as database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2010
    Location
    Brussels
    Posts
    26

    [tutorial] webbased guestbook with Excel as database

    If you want to make a guestbook or other kind of messaging system for a web application, and you don't have a database available like MySQL with PHP or with ASP, then I have this free solution for you.
    You can use Excel as a database, it's not supposed to serve as database for a web application, but technically it is possible.
    So if your company ran out of budget or tools for a good database, read this page very carefully.

    Note: it bugs when ActiveX Controls are disabled, it works only in IE in a Windows environment, and it does not work online because of browser securities. But it is a cheap and neat solution for an intraweb or intranet.
    Languages: (x)html, VBScript, Javascript, CSS.


    Setting up your Excel file
    Create an Excel file with the amount of columns you need for the data you need.
    In this simple example, we just need 4 columns, for the date, name, e-mail and message.


    Making up your html form
    First of all, we need a form to let visitors write data to the guestbook. Notice that we don't add a field for the date, because the date will be generated by your VBScript afterwards.
    With a simple command, you can let the form call your VBScript function
    onclick="VBscript:addEntry()"
    Make sure you don't add a semicolon at the end, as you do in Javascript.

    If you don't like it hardcoded, you can make it up more neat, with unobtrusive javascript outside of your form. In that case you'll need a Javascript that catches the ID of your input field and add an event handler to it that triggers your VBScript. In this tiny example it's not necessary to add these lines of code. Click here for more details.

    Code:
    <form name="myguestbook" method="post" id="myguestbook"> 
    <p><label for="name">Name: </label><input name="name" type="text" /></p>
    <p><label for="name">Email: </label><input name="email" type="text" /></p>
    <p><label for="message">Message: </label><textarea name="message"></textarea></p>
    <p>
    <label for="submission"></label>
    <input type="submit" name="submission" value="SEND" onclick="VBscript:addEntry()" />
    </p>
    </form>
    VBScript to add data to the Excel file
    I hope this is clear with the comments inside:

    Code:
    Function addEntry()
    ' initialize excel variables:
    
    
      Dim strYourFile : strYourFile   = "C:/Write Full Path/Your Folder/guestbook.xls"
      Dim strYourSheet : strYourSheet = "Sheet1"
    ' instantiate excel object:
    
    
      Dim objExcel : Set objExcel     = CreateObject("Excel.Application")
      objExcel.DisplayAlerts          = False
      objExcel.Visible                = False
      set objWorkbook                 = objExcel.Workbooks.Open(strYourFile)
      set objWorksheet                = objWorkbook.Worksheets(strYourSheet)
    ' create the rest of your variables, do not define them like in VBA
    
    
    Dim strName, strEmail, strMessage, strDate, intNewRow
    ' pass the values from the form to a variable in VBScript:
    
    
    strName    = document.myguestbook.name.value
    strEmail   = document.myguestbook.email.value
    strMessage = document.myguestbook.message.value
    ' to add a date to each entry in your guestbook:
    
    
    strDate    = Date
    ' to add the entry to the first new blank row in your sheet:
    
    
    set objRange = objWorksheet.UsedRange
    intNewRow = objRange.Rows.Count + 1
    
    objWorksheet.Cells(intNewRow, 1).Value = strDate
    objWorksheet.Cells(intNewRow, 2).Value = strName
    objWorksheet.Cells(intNewRow, 3).Value = strEmail
    objWorksheet.Cells(intNewRow, 4).Value = strMessage
    ' save file, disconnect sheet from workbook, close file and excel app:
    
    
    objWorkbook.Save
    Set objWorksheet = Nothing
    objWorkbook.Close
    set objWorkbook = Nothing
    objExcel.Quit
    set objExcel = Nothing
    
    MsgBox "Your message is added correctly!"
    
    End Function
    VBScript to retrieve data from the Excel file
    I hope this is clear with the comments inside:


    Code:
    Function viewEntry()
    ' initialize excel variables:
    
    
      Dim strYourFile : strYourFile   = "C:/Write Full Path/Your Folder/guestbook.xls"
      Dim strYourSheet : strYourSheet = "Sheet1"
    ' instantiate excel object:
    
    
      Dim objExcel : Set objExcel     = CreateObject("Excel.Application")
      objExcel.DisplayAlerts          = False
      objExcel.Visible                = False
      set objWorkbook                 = objExcel.Workbooks.Open(strYourFile)
      set objWorksheet                = objWorkbook.Worksheets(strYourSheet)
    ' create the rest of your variables, do not define them like in VBA:
    
    
    Dim strName, strEmail, strMessage, strDate, intNewRow
    ' sort method settings:
    
    
      Const xlAscending = 1   
      Const xlDescending = 2
      Const xlYes = 1        ' if the first excel row is a header, use 1
      Const xlNo = 2  
    
      Set objRange = objWorksheet.UsedRange
      Set sortA = objWorksheet.Range("A:A")  'sort per date, by column 1
      objRange.Sort sortA, xlDescending, , , , , , xlNo
    
      Dim strEntries, intBeginRow, strListMessages, intAmountMessages, ntrMsg
    ' to change the special enter chars in your excel messages to html readable enter chars:
    
    
      Dim objRegExp  
      Set objRegExp = New RegExp
      objRegExp.Pattern = "[\r]"
      objRegExp.Global = True 
    
      ntrMsg = ""
      strEntries = "<ul class='entries'>"
      intBeginRow = 1
    ' loop through every entry or row in excel 
    ' column1: date, column2: name, column3: email, column4: message
    
    
      Do Until objRange.Cells(intBeginRow,1).Value = ""
    ' at each enter in the message, change the enter so it's readable:
    
    
           ntrMsg = objRange.Cells(intBeginRow,4).Value
           ntrMsg = objRegExp.Replace(ntrMsg,"<br />")
       strEntries = strEntries & "<li class='entry'><ul>" _
                     & "<li class='date'>" & objRange.Cells(intBeginRow,1).Value & "</li>" _
                     & "<li class='name'><a href='mailto:" & objRange.Cells(intBeginRow,3).Value & "'>" _
                     & objRange.Cells(intBeginRow,2).Value & "</a></li>" _
                     & "<li class='message'>" & ntrMsg & "<br /></li>" _
                     & "</ul></li> &#37;26nbsp;"
       intBeginRow = intBeginRow + 1
       ntrMsg = ""
      Loop
      
      strEntries = strEntries & "</ul>"
    ' make an Array of all your entries:
    
    
      strEntries = split(strEntries,"%26nbsp;")
    ' if you want to show up only the last 10 messages on the page, change: inAmountMessages = 10:
    
    
      intAmountMessages = UBound(strEntries)
    
      For i = 0 to intAmountMessages
      strListMessages = strListMessages & strEntries(i)
      Next
    ' disconnect sheet from workbook, close file and excel app:
    
    
    Set objWorksheet = Nothing
    objWorkbook.Close
    set objWorkbook = Nothing
    objExcel.Quit
    set objExcel = Nothing
    ' send the list of messages to a javascript that lets the entries show up in html:
    
    
      sendThrough(strListMessages)
    
    End Function
    Add some Javascript to glue your VBScript in your (x)html page
    Code:
    // make sure you put this messages DIV in your html body: <div id="messages"></div>
    // span classthis function receives the list of messages to show up in your html:
    
    
    function sendThrough(strListMessages){
     var messContainer = document.getElementById("messages");
     messContainer.innerHTML = strListMessages;
    }
    // this rule is needed to load your VBScript:
    
    
    window.onload = function(){ viewEntry(); }
    Style your guestbook with CSS
    Add a minimum style to your form fields, note that I kept it sober:

    HTML Code:
    <style>
    input, textarea, select { position: relative; margin-left: 100px; width: 350px; }
    input { border: 1px #000000 solid; }
    textarea { height: 150px; border: 1px #000000 solid; }
    label { position: absolute; width: 100px; text-align: right; padding-right: 10px; }
    </style>
    Add a minimum style to your messages to display:

    HTML Code:
    <style>
    .entry { list-style-type: none; background-color: #999999; border: 1px solid #000000; 
             margin-bottom: 10px; width: 500px; }
    .date, .name, .message { display: inline; list-style-type: none; padding: 5px; }
    .message { float: left; font-style: italic; }
    </style>
    Demo
    http://bulevardi.be/?content=scripting&example=exvb2#6
    As the security settings in your browser do not allow to run this online, I put the whole script and excel file in a .zip file, which you can download right above.

    Thanks for reading,
    Bulevardi!

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Oct 2010
    Location
    Brussels
    Posts
    26

    Re: [tutorial] webbased guestbook with Excel as database

    Can someone tell me if the property .Value is really necessary?

    objWorksheet.Cells(intNewRow, 2).Value = strName

    This works aswel:
    objWorksheet.Cells(intNewRow, 2).Text = strName
    Or even:
    objWorksheet.Cells(intNewRow, 2) = strName

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.