Search code examples
excelvbscripthtavba

VBS code not working in HTA (WScript declaration)

I'm currently working on a developing an interface that gives users the option to select which report they would like to generate but I'm having issues with my the declaration of "WScript".

I have attached the error as well. As i've fixed spacing between lines don't pay attention to the line number - I've bolded the line that I'm erroring at. Really appreciate any help (Did the best i could with formatting my question correctly).

<html>
  <title>Report Generation</title>
  <head>
  <HTA:APPLICATION 
    APPLICATIONNAME="Master Report Generation"
  SCROLL="yes"
  SINGLEINSTANCE="yes"
  WINDOWSTATE="normal">
  </head>

<style>
BODY
{
background-color: buttonface;
Font: arial,sans-serif
margin-top: 10px;
margin-left: 20px;
margin-right: 20px;
margin-bottom: 5px;
}
.button
{
width: 91px;
height: 25px;
font-family: arial,sans-serif;
font-size: 8pt;
}
td
{
font-family: arial,sans-serif;
font-size: 10pt;
}                     
#scroll
{
height:100%;
overflow:auto;
}
SELECT.FixedWidth 
{
width: 17em;  /* maybe use px for pixels or pt for points here */
}
</style>


<script language="vbscript">

Option Explicit    

Dim WinWidth : WinWidth = 350
Dim WinHeight : WinHeight = 250
Window.ResizeTo WinWidth, WinHeight

  Sub CheckBoxChange

    If CheckBox(0).Checked Then
    ExecuteScoreCard()
    Else
    MsgBox "CheckBox is not checked"
    End If

  End Sub

  Sub ExecuteScoreCard() 
    Dim sitecode
    Dim objExcel  
    Dim objApp  
    Dim objWorkbook
    Dim objSheet
    Dim scriptdir
    Dim oFSO
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    <b>scriptdir = oFSO.GetParentFolderName(WScript.ScriptFullName)</b>

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open(scriptdir & "\SCORECARD.xlsm")
    Set objSheet = objWorkbook.Worksheets("Cover Tab")  

    objSheet.Cells(4, 2) = sitecode

    objExcel.Run "RefreshConns"
    WScript.Sleep 75000 

    objExcel.ActiveWorkbook.SaveAs scriptdir & "\Scorecards\" & "SCORECARD_" & sitecode & "_" & Year(Now()) & Month(Now()) & Day(Now()) & "_" & Hour(Now()) & Minute(Now()) &".xlsm", 52
    objExcel.ActiveWorkbook.Close
    objExcel.Quit   

    MsgBox("Successfully generated scorecard.")
End Sub 

</script>

<body>
Site Code: <input type="inputbox" name="sitecode">
  <br>
  <input type="checkbox" name="CheckBox"> Scorecard
  <br>
  <input type="checkbox" name="CheckBox"> Report2
  <br>
  <input type="checkbox" name="CheckBox"> Report3
  <br>
  <br>
  <input type="submit" name="accept" value="Submit" onclick="CheckBoxChange">
</body>
</html>

enter image description here

Solution

  • The built-in WScript object is there when you run a script using the Windows Script Host (cscript.exe or wscript.exe). On the other hand, an HTA file is basically running IE (though in a mode that allows for local file system and COM object access). There's no built-in WScript object, although the COM objects that one can use from WSH one can also use from within an HTA (see this article I found quickly on using the WScript.shell object, for example).

    You need to do things in a more web-like way within an HTA. Use setTimeout or something similar to delay functionality until later. I did a quick search for how to get the current path from within an HTA and found a page, though there are probably other approaches.

    See also this related question.