?
Solved

Any help with a web page creation which will update the data entered to a excel sheet

Posted on 2007-08-12
2
Medium Priority
?
290 Views
Last Modified: 2010-03-05
Hi,

I want a form to enter user information.Ex:
I shall send the link to some of my users and when they enter the data it has to update in the excel sheet.

Webpage details.

Emp ID                Username      NT Login     Email id      and so on say 10 boxes and a submit button.Once the user clicks submit it should update the webpage.

Once submitted the submit button should get disabled.


Regards
Sharath
0
Comment
Question by:bsharath
2 Comments
 
LVL 13

Expert Comment

by:dhoffman_98
ID: 19679438
This is another question fro bsharath that is posted in the wrong zone.
Post your questions in the correct zone and you stand a better chance of getting answers.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 19797776
Following this post:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_22790935.html?cid=238#a19797746

you can use an HTA application with the following code that will store the information to a user's text file in the location speicifed by strTextLogPath in the Window_OnLoad procedure (not the global, empty, one) :
'========================
<head>
<title>User Information Form</title>
<HTA:APPLICATION
     APPLICATIONNAME="User Information Form"
     BORDER="thin"
     SCROLL="no"
     SINGLEINSTANCE="yes"
     WINDOWSTATE="normal"
>
</head>

<script language="VBScript">

' Global variable to hold the path to the text files
' they are defined here to be global, but are assigned
' values in the Window_OnLoad procedure
strTextLogPath = ""
strLogFile = ""

Sub Window_OnLoad
      intWidth = 800
      intHeight = 600
      Me.ResizeTo intWidth, intHeight
    Me.MoveTo ((Screen.Width / 2) - (intWidth / 2)),((Screen.Height / 2) - (intHeight / 2))
    cbo_building.Style.Width = 150
      Set objNetwork = CreateObject("WScript.Network")
      txt_loginname.Value = objNetwork.UserName
      txt_machinename.Value = objNetwork.ComputerName

      ' This line gets the path from the currently running directory of this HTA
      strTextLogPath = Mid(Replace(Replace(document.URL, "file://", ""), "%20", " "), 1, InStrRev(Replace(Replace(document.URL, "file://", ""), "%20", " "), "\"))
     
      If Right(strTextLogPath, 1) <> "\" Then strTextLogPath = strTextLogPath & "\"
      ' This line appends username.txt to that path
      strLogFile =  strTextLogPath & txt_loginname.Value & ".txt"
      ' This retrieves the user name for the user
      Set objADSysInfo = CreateObject("ADSystemInfo")
      txt_name.Value = Replace(Split(objADSysInfo.UserName, ",")(0), "CN=", "")
      Set objUser = GetObject("LDAP://" & objADSysInfo.UserName)
      txt_email.Value = objUser.Mail
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      If objFSO.FileExists(strLogFile) Then
            MsgBox "Your information has already been submitted.  Thank you."
            window.Close
      End If
      Set objFSO = Nothing
End Sub

Sub Default_Buttons
      If Window.Event.KeyCode = 13 Then
            btn_submit.Click
      End If
End Sub

Sub Submit_Form
      If txt_name.Value = "" Then
            MsgBox "Please enter your full name in the Name field."
            txt_name.Focus
      ElseIf txt_empid.Value = "" Then
            MsgBox "Please enter your Employee ID in the Emp ID field."
            txt_empid.Focus
      ElseIf cbo_building.Value = "opt_none" Then
            MsgBox "Please select your building from the Building drop down box."
            cbo_building.Focus
      ElseIf txt_seatno.Value = "" Then
            MsgBox "Please enter your seat number in the Seat No field."
            txt_seatno.Focus
      ElseIf txt_designation.Value = "" Then
            MsgBox "Please enter your designation in the Designation field."
            txt_designation.Focus
      ElseIf cbo_location.Value = "opt_none" Then
            MsgBox "Please select your location from the Location drop down box."
            cbo_location.Focus
      ElseIf txt_extensionno.Value = "" Then
            MsgBox "Please enter your extension number in the Extension No field."
            txt_extensionno.Focus
      Else
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objOutputFile = objFSO.CreateTextFile(strLogFile, True)
            'objOutputFile.Write """Full Name;Login Name;Employee ID;Seat Number;Designation;Machine Name;Extension Number"""
            'objOutputFile.Write VbCrLf & """" & txt_name.Value & ";" & txt_loginname.Value & ";" & txt_empid.Value & ";" & txt_seatno.Value & ";" & txt_designation.Value & ";" & txt_machinename.Value & ";" & txt_extensionno.Value & """"
            objOutputFile.Write "Full Name;Login Name;Email;Employee ID;Building;Seat Number;Designation;Location;Machine Name;Extension Number"
            objOutputFile.Write VbCrLf & txt_name.Value & ";" & txt_loginname.Value & ";" & txt_email.Value & ";" & txt_empid.Value & ";" & cbo_Building.Value & ";" & txt_seatno.Value & ";" & txt_designation.Value & ";" & cbo_Location.Value & ";" & txt_machinename.Value & ";" & txt_extensionno.Value
            objOutputFile.Close
            Set objOutputFile = Nothing
            Set objFSO = Nothing
            MsgBox "Thank you for submitting your information."
            window.Close
      End If
End Sub

Sub Validate_Keys(strField)
      If LCase(strField) = "designation" Then
            If Window.Event.KeyCode >= 48 And Window.Event.KeyCode <= 57 Then Window.Event.KeyCode = Null
      ElseIf LCase(strField) = "extensionno" Then
            If (Window.Event.KeyCode < 48 Or Window.Event.KeyCode > 57) And Window.Event.KeyCode <> 13 Then Window.Event.KeyCode = Null
      End If
End Sub

</script>

<body STYLE="font:14 pt arial; color:white;filter:progid:DXImageTransform.Microsoft.Gradient
(GradientType=1, StartColorStr='#000033', EndColorStr='#0000FF')" onkeypress='vbs:Default_Buttons'>
      <table width='90%' height = '100%' align='center' border='0'>
            <tr>
                  <td align='center'>
                        <h3>User Information Form</h3><br>
                  </td>
            </tr
            <tr>
                  <td align='center'>
                        <table>
                              <tr>
                                    <td>
                                          Name:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="30" size="40" id="txt_name" name="txt_name">
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Login Name:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="30" size="40" id="txt_loginname" name="txt_loginname" readonly>
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Email Address:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="50" size="70" id="txt_email" name="txt_email">
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Emp ID:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="5" size="10" id="txt_empid" name="txt_emptid">
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Building:
                                    </td>
                                    <td>
                                          <select size="1" id="cbo_building" name="cbo_building">
                                                <option id="opt_none" value="opt_none" selected> --- Select Building --- </option>
                                                <option id="opt_shafika" value="Shafika">Shafika</option>
                                                <option id="opt_hafizcourt" value="Hafiz Court">Hafiz Court</option>
                                                <option id="opt_grplaza" value="GR Plaza">GR Plaza</option>
                                                <option id="opt_hafizfort" value="Hafiz Fort">Hafiz Fort</option>
                                                <option id="opt_titustowers" value="Titus Towers">Titus Towers</option>
                                          </select>
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Seat No:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="10" size="15" id="txt_seatno" name="txt_seatno">
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Designation:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="30" size="40" id="txt_designation" name="txt_designation" onkeypress="vbs:Validate_Keys('designation')">
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Location:
                                    </td>
                                    <td>
                                          <select size="1" id="cbo_location" name="cbo_location">
                                                <option id="opt_none" value="opt_none" selected> --- Select Location --- </option>
                                                <option id="opt_chennai" value="Chennai">Chennai</option>
                                                <option id="opt_hyderabad" value="Hyderabad">Hyderabad</option>
                                          </select>
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Machine Name:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="16" size="25" id="txt_machinname" name="txt_machinename" readonly>
                                    </td>
                              </tr>
                              <tr>
                                    <td>
                                          Extension No:
                                    </td>
                                    <td>
                                          <input type="text" maxlength="4" size="10" id="txt_extensionno" name="txt_extensionno" onkeypress="vbs:Validate_Keys('extensionno')">
                                    </td>
                              </tr>
                        </table>
                  </td>
            </tr>
            <tr>
                  <td align='center'>
                        <br>Please note that all fields are required.
                  </td>
            </tr>            
            <tr>
                  <td align='center'>
                        <input type="button" value="Submit" name="btn_submit"  onClick="vbs:Submit_Form"><br><br>
                  </td>
            </tr>
            <tr>
                  <td align="right">
                        This information is required to keep<br>
                        your details up to date in our database
                  </td>
            </tr>
      </table>
</body>
'==================



And a VBS file with the following code to combine all of the user's text files into Excel, again, updating
strWorkBook = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "User_Info.xls"
strUserInfoFolder = "."

to reflect the name of the new XLS file, and the path to the user's text files.
'=====================
Option Explicit
' Combine_User_Info_To_Excel.vbs
Dim strWorkBook, strUserInfoFolder, objFSO, objExcelApp, objFile, objOpenFile, arrSingleLine, intCount, intRow, strLine

Const intForReading = 1
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105

' Change the line below to the path where you would like the Excel spreadsheet to be saved.
strWorkBook = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "User_Info.xls"
' Change the line below to the full UNC path to the share that the text files are written to.
strUserInfoFolder = "."

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Create Excel Spreadsheet
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
objExcelApp.Workbooks.Add
objExcelApp.ScreenUpdating = False
objExcelApp.Calculation = xlCalculationManual
While objExcelApp.Sheets.Count > 1
      objExcelApp.Sheets(objExcelApp.Sheets.Count).Delete
Wend

objExcelApp.ActiveWorkbook.Sheets(1).Activate
objExcelApp.ActiveSheet.Rows(1).Font.Bold = True

intRow = 1

For Each objFile In objFSO.GetFolder(strUserInfoFolder).Files
      'Reset back to column A for each file
      If LCase(Right(objFile.Name, 4)) = ".txt" Then
            Set objOpenFile = objFSO.OpenTextFile(objFile.Name, intForReading, False)
            While Not objOpenFile.AtEndOfStream
                  strLine = objOpenFile.ReadLine
                  If Len(strLine) > 0 Then
                        arrSingleLine = Split(strLine, ";")
                        If objExcelApp.ActiveSheet.Range("A1").FormulaR1C1 <> arrSingleLine(0) Then
                              For intCount = LBound(arrSingleLine) To UBound(arrSingleLine)
                                    objExcelApp.ActiveSheet.Cells(intRow, intCount + 1).FormulaR1C1 = arrSingleLine(intCount)
                              Next
                              intRow = intRow + 1
                        End If
                  End If
            Wend
      End If
Next

'Autofit all columns
objExcelApp.ActiveSheet.Columns.EntireColumn.AutoFit

' Save Excel File
objExcelApp.DisplayAlerts = False
objExcelApp.ActiveWorkbook.SaveAs strWorkBook
objExcelApp.DisplayAlerts = True
objExcelApp.ScreenUpdating = True
objExcelApp.Calculation = xlCalculationAutomatic
objExcelApp.ActiveWorkbook.Close False
objExcelApp.Quit

MsgBox "Done"
'=====================]





Then, if you wish, you can encode the VBScript bits of the HTA by doing this:

Download the Microsoft Script Encoder and install it from here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=E7877F67-C447-4873-B1B0-21F0626A6329

Then put this line:
'**Start Encode**

underneath this line
<script language="VBScript">

so it looks like this:
<script language="VBScript">
'**Start Encode**


Then, rename the HTA extension to HTM, and run this command:
C:\Program Files\Windows Script Encoder>screnc <filename>.htm <filename>.hta

where <filename> is replaced with the full path your file.

Then, the HTA that gets created should be encoded.

Please bear in mind though, this is not entirely secure (from that MS website):
"the important thing to keep in mind is that the script is simply encoded (or obfuscated); it is definitely not encrypted. What does that mean? That means the encoder will hide your script from most people; however, a truly determined hacker - armed with a knowledge of codes or armed with a utility downloaded from the Internet - could crack the code. Among other things, that means that you should never do something like hide an Administrator password in a script and assume that the Script Encoder will keep it safe from prying eyes. It wont. Its an encoder, not an encrypter, and theres definitely a difference"

Regards,

Rob.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Remote Apps is a feature in server 2008 which allows users to run applications off Remote Desktop Servers without having to log into them to run the applications.  The user can either have a desktop shortcut installed or go through the web portal to…
Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question