-
Mar 9th, 2011, 04:08 PM
#1
Thread Starter
Junior Member
[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> %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!
-
Apr 5th, 2011, 09:23 AM
#2
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|