Start Free Trial

Christopher Wright

asked on

Run Batch with HTA Button

Currently, I have an HTA that is fed with data from an excel spreadsheet. This spreadsheet is generated with an Oracle middleware program called Discoverer. So basically, I have a batch file that runs the Discoverer program at night and exports to an .xls spreadsheet. The HTA then connects to the spreadsheet via ADODB. My question is if it would be possible to remove the step of exporting to an .xls altogether since the Discoverer program has the ability to export to .htm and .xml? My thought is that I run the Discoverer program behind the scenes and simply have the HTA refresh the query with a button click. Is this sort of thing possible? Or better yet, is this sort of thing even practical? Thanks for any suggestions.

HTA Script (Named: Index.hta)
Style Sheet (Named: mystyle.css)
Search Script (Named: search.vbs)
Form Script (Named: forms.vbs)
Batch Script (Named: batch.vbs)
Original Batch Script (Named: batch.bat)
Daily-Stocking-Position.xls

Hi, it sounds like it would be possible. So the batch file that you run must run a Discoverer command that outputs to XLS, right? If you can just change that command to export to HTML instead, then you should be able to have a "Run" button on your HTA that does something like

Sub RunCode
Set objShell = CreateObject("WScript.Shell")
objShell.Run "discoverer.exe -exporttype HTML -exportpath \\server\share\MyReport.html", 0, True
objShell.Run "iexplore.exe ""\\server\share\MyReport.html""", 1, False
End Sub

Open in new window

that would run the export, and open the HTML page each time you click that button on the HTA.

Regards,

Rob.

Christopher Wright

ASKER

Hi Rob! Thanks for the feedback. The Discoverer report currently exports to XLS but has the capabilities to export to HTM. So, with using your script, this can be automated to pull directly? Taking this a step further, could I replace the source for the dropdown altogether. In my 'forms.vbs' script, the dropdown is fed with an existing XLS at the on_load event. So, could this run the program, query the htm report, and then feed the dropdown values?

So, forgive my ignorance but what exactly must I modify, besides the report name...? I changed it below and adjusted the file extension to match exactly what the program has. (.HTM)

Sub RunCode
Set objShell = CreateObject("WScript.Shell")
objShell.Run "discoverer.exe -exporttype HTML -exportpath \\server\share\Vendors.htm", 0, True
objShell.Run "iexplore.exe ""\\server\share\Vendors.htm""", 1, False
End Sub

Open in new window

I can't see any of your VBS or HTA code, so I'm not sure at what point the Discoverer program is initiated to export to XLS. I'm also not sure what the drop down selection is built from in the HTA. My assumption is that the Discoverer program is being called via a batch command (that can be transferred to run from the HTA) that is exporting to XLS. How would you go about making Discoverer export to HTML?

Obviously XLS and HTML are completely different formats, so if you HTA is built to present a custom interface to that data, then you would have to rewrite the code to still present the drop down options from the raw HTML data.....I'm not sure if this would save you any time.

I was more thinking of it from a point of view where you would export the data to HTML, and then just dislay the static HTML report as it was exported (which is what the second objShell.Run statement would do).

I probably do need to see a bit more of the interface...even if was just screenshots...to see more of what you would like to achieve.

Regards,

Rob.

Christopher Wright

ASKER

My apologies Robb. For some reason, the code snippets were removed when I edited my original question. Please forgive my oversight! I will fix this ASAP!!

Christopher Wright

ASKER

HTA Script (Named: Index.hta)

<html> 
<head> 
<HTA:APPLICATION 
	APPLICATIONNAME="Purchasing Portal"
	ID="Purchasing Portal"
	border="normal" 
	borderStyle="normal" 
	caption="yes" 
	icon="" '"I:\BI-CEW\favicon2.ico" 
	maximizeButton="yes" 
	minimizeButton="yes" 
	showInTaskbar="no" 
	windowState="Maximize"
	innerBorder="yes"
	navigable="yes"
	scroll="auto"
	SINGLEINSTANCE="yes"
	scrollFlat="yes" 
/>

<br>
<title>Purchasing Portal -- Presented by ADS Solutions</title>
<center>
<TABLE BORDER="1" WIDTH="1000"><TR><TD>
<center><u><H1>Inventory Stock Position</H1></u></center>
<hr>
</center>
<link rel="stylesheet" type="text/css" href="css/mystyle.css">
<script language="VBScript" src="scripts/forms.vbs"></script> 
<script language="VBScript" src="scripts/search.vbs"></script> 
<script language="VBScript" src="scripts/batch.vbs"></script> 
</head>

<!--Page layout follows--> 
<body onmouseover="menu_onmouseover" style="font:14pt Cambria"> 
<div id="menu" style="position:absolute;left:0;top:0;width:145%;height:23px; 
                    padding-top:2px;background-color:lightgrey; 
                    font:normal 12pt Cambria;z-Index:100"> 
</div> 
<span id="dropmenu" style="font:normal 10pt Cambria"></span>

<fieldset>
<b><legend>Search Criteria:</legend></b><p>
<b>Part Number: </b> <input type="text" name="txtFilter" size="40"><br><br>
<b>Vendor Name: <select size="1" name="OptionChooser" onChange=""></select>

<br><br>
<input id="runbutton1"  class="button" type="button" value="Search" name="run_button"  onClick="DecideSearch" title="Click here to search specific values.">
<input id="runbutton3"  class="button" type="button" value="Refresh" name="run_button"  onClick="RunScript" title="Click here to refresh page.">
<input id="runbutton3"  class="button" type="button" value="Batch" name="run_button"  onClick="RunCode" title="Click here to refresh page.">
<br>
</fieldset>
<hr>
<span id=DataArea></span>
<span id=msg style="z-Index:10"></span> 
</body> 
</html>

Open in new window

Style Sheet (Named: mystyle.css)

BODY
{
   background-color: Gainsboro;
   font-family: Cambria;
   font-size: 12pt;
   margin-top: 30px;
   margin-left: 5px;
   margin-right: 5px;
   margin-bottom: 30px;
}
button
{
   background-color: Steelblue;
   font-family: Cambria;
   font-size: 12pt;
   width: 100px;
   margin-left: 0px;
}
textarea
{
   color: white;
   font-family: Cambria;
   font-size: 11pt;
}
select
{
   font-family: Cambria;
   font-size: 10pt;
   width: 300px;  
   margin-left: 0px;
}
td
{
   color: default;
   font-family: Cambria;
   font-size: 11pt;
}

Open in new window

Search Script (Named: search.vbs)

' fields for connection string
Dim dataSrc

dataSrc = "I:\Chris Wright\Chris Wright\Daily Reports\Stock Position Reports\Daily Stocking Position.xlsx"
'dataSrc = "I:\Chris Wright\Chris Wright\Daily Reports\Vendors.htm"
'dataSrc = "c:\users\rob\documents\ee\hta\Daily-Stocking-Position-Test-Dat.xlsx"

' Perform an exact search
sub ReturnRows
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Dim Cmd
	
	
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	'cn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db01.ads.ad; Initial Catalog=prod;" & "User ID=crwright;Password=cew5310;"
	
	cn.Open
	'Cmd.ActiveConnection = cn
	Rs.Open "Select * from [VPN Position$] Where [Vendor Part Number] = """ & txtFilter.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
        if not rs.eof then
	  strData = strData & "<tr>"
          strRow = ""
          For each fld in rs.fields
            strRow = strRow & "<th>" & fld.name & "</th>"
          Next
          strData = strData & strRow & "</tr>"
        end if 
	do until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		if fld.name = "MAX Ordered Qty" then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		else
			On Error Resume Next
			strRow = strRow & "<td>" & fld.value & "</td>"
			On Error Goto 0
		end if
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop
	DataArea.InnerHTML = strData & "</table>"
end Sub

sub VendorName
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select * from [VPN Position$] Where [Vendor Name] =""" & Optionchooser.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
	If Not Rs.EOF Then
	  strData = strData & "<tr>"
	  strRow = ""
	  For Each fld in Rs.fields
		strRow = strRow & "<th>" & fld.name & "</th>"
	  Next
	  strData = strData & strRow & "</tr>"
	End If 

	Do Until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		If fld.name = "MAX Ordered Qty" Then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		Else
			On Error Resume Next
			strRow = strRow & "<td>" & fld.value & "</td>"
			On Error Goto 0
		End If
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop

	DataArea.InnerHTML = strData & "</table>"
End Sub

Sub Combination
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select * from [VPN Position$] Where [Vendor Part Number] = '" & txtFilter.value & "' AND [Vendor Name] =""" & Optionchooser.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
	If Not Rs.EOF Then
	  strData = strData & "<tr>"
	  strRow = ""
	  For Each fld in Rs.fields
		strRow = strRow & "<th>" & fld.name & "</th>"
	  Next
	  strData = strData & strRow & "</tr>"
	End If 

	Do Until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		If fld.name = "MAX Ordered Qty" Then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		Else
			On Error Resume Next
			strRow = strRow & "<td>" & fld.value & "</td>"
			On Error Goto 0
		End If
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop

	DataArea.InnerHTML = strData & "</table>"
End Sub

Sub DecideSearch
	' txtFilter is PART NUMBER
	' OptionChooser is VENDOR
	
	' Search the vendor if the part number is empty and a Vendor has been selected
	If txtFilter.value = "" AND OptionChooser.SelectedIndex > 0 Then
	 	VendorName
	ElseIf txtFilter.value <> "" AND OptionChooser.SelectedIndex = 0 Then
		ReturnRows
	Else
		' Search based on a combination of the two
		Combination
	End if
End Sub

Sub FuzzySearch
	' Search the vendor if the part number is empty and a Vendor has been selected
	' Also do not search if both are empty
	If txtFilter.value = "" AND OptionChooser.SelectedIndex > 0 THEN
	 	VendorName
	ElseIf txtFilter.value <> "" Then
		FuzzySearchGo
	Else
		CombinationFuzzy
	End if
End Sub

Sub FuzzySearchGo
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select * from [VPN Position$] Where [Vendor Part Number] Like '%" & txtFilter.value & "%'", cn, 1, 3
	strData = "<table width=""100%"" border>"
        if not rs.eof then
	  strData = strData & "<tr>"
          strRow = ""
          For each fld in rs.fields
            strRow = strRow & "<th>" & fld.name & "</th>"
          Next
          strData = strData & strRow & "</tr>"
        end if 
	do until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		if fld.name = "MAX Ordered Qty" then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		else
			On Error Resume Next
			strRow = strRow & "<td>" & fld.value & "</td>"
			On Error Goto 0
		end if
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop
	DataArea.InnerHTML = strData & "</table>"
End Sub

Sub CombinationFuzzy
	Dim cn
	Dim Rs
	Dim strData
	Dim strRow
	Dim fld
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select * from [VPN Position$] Where [Vendor Part Number] Like '%" & txtFilter.value & "%' AND [Vendor Name] =""" & Optionchooser.value & """", cn, 1, 3
	strData = "<table width=""100%"" border=""2"" cellpadding=""1""<caption>Results:</caption"">"
	If Not Rs.EOF Then
	  strData = strData & "<tr>"
	  strRow = ""
	  For Each fld in Rs.fields
		strRow = strRow & "<th>" & fld.name & "</th>"
	  Next
	  strData = strData & strRow & "</tr>"
	End If 

	Do Until Rs.EOF
	  strRow = "<tr>"
	  For Each fld in Rs.Fields
		If fld.name = "MAX Ordered Qty" Then
			strRow = strRow & "<td class=""mono"">" & fld.value & "</td>"
		Else
			On Error Resume Next
			strRow = strRow & "<td>" & fld.value & "</td>"
			On Error Goto 0
		End If
	  Next
	  strData = strData & strRow & "</tr>"
	  Rs.MoveNext
	Loop

	DataArea.InnerHTML = strData & "</table>"
End Sub

Sub RunCode
	Set objShell = CreateObject("WScript.Shell")
	objShell.Run "dis51usr.exe -exporttype HTML -exportpath \\server\share\Vendors.htm", 0, True
	objShell.Run "iexplore.exe ""\\server\share\Vendors.htm""", 1, False
End Sub

'============================================

Open in new window

Form Script (Named: forms.vbs)

' Copyright 2013, Chris Wright, cwright at adsinc dot com 
 
' Define menu items 
Const sMenuItems = "File,Edit,Help" 
 
' Define one submenu constant for each menu item as illustrated below 
' Each is a comma separated list in a single string 
Const sFile = "Open,Close,Save,Save As ...,Exit" 
Const sEdit = "Cut,Copy,Paste,Select All,Deselect All" 
Const sHelp = "Help, About" 
Const sHTML = "&nbsp;&nbsp;&nbsp;#sItem#&nbsp;&nbsp;&nbsp;" 
 
Dim dMenus, sMenuOpen 
 
Sub Window_onload 

  LoadDropDown 
	
  Dim entry 
  set dMenus = createObject("Scripting.Dictionary") 
  For Each entry in Split(sMenuItems, ",") 
    menu.innerHTML = menu.innerHTML & "&nbsp;<span id=" & entry _ 
                   & " style='padding-bottom:2px' onselectstart=cancelEvent>&nbsp;" _ 
                   & entry & "&nbsp;</span>&nbsp;&nbsp;" 
    dMenus.Add entry, Split(eval("s" & entry), ",") 
  Next 
  sMenuOpen = "" 

end sub  
 
Sub menu_onmouseover 
  
  clearmenu 
  with window.event.srcElement 
    if .parentElement.ID = "menu" then 
      .style.border = "thin outset" 
      .style.cursor = "arrow" 
    end if 
  end with 
 
end sub 
 
Sub menu_onmouseout 
 
  with window.event.srcElement 
    .style.border = "none" 
    .style.cursor = "default" 
  end with ' srcElement 
 
end sub 
 
Sub dropmenu_onmouseover 
  
  with window.event 
    .srcElement.style.cursor = "arrow" 
    .cancelbubble = true 
    .returnvalue = false 
  end with 
 
end sub 
 
sub SubMenuOver 
 
  with window.event.srcElement 
    if .ID = "dropmenu" then exit sub 
    .style.backgroundcolor = "darkblue" 
    .style.color = "white" 
    .style.cursor = "arrow" 
  end with 
 
end sub 
 
sub SubMenuOut 
 
  with window.event.srcElement 
    .style.backgroundcolor = "lightgrey" 
    .style.color = "black" 
    .style.cursor = "default" 
  end with 
 
end sub 
 
Sub menu_onclick 
Dim oEL, oItem 
 
  if sMenuOpen <> "" then exit sub 
  with window.event.srcElement 
    if .ID <> "menu" then 
      .style.border = "thin inset" 
      nLeft = .offsetLeft 
      ntop  = .offsetTop + replace(menu.style.Height, "px", "") - 5 
      sMenuOpen = trim(.innertext) 
      with dropmenu 
        with .style 
          .border = "thin outset" 
          .backgroundcolor = "lightgrey" 
          .position = "absolute" 
          .left = nLeft 
          .top = nTop 
          .width = "100px" 
          .zIndex = "101"  
        end with ' style 
        for each sItem in dMenus.Item(sMenuOpen) 
          set oEL = document.createElement("SPAN") 
          .appendChild(oEL) 
          with oEl 
            .ID = sItem 
            .style.height = "20px" 
            .style.width = dropmenu.style.width 
            .style.zIndex = "102" ' added 28 June 2010 
            .innerHTML = Replace(sHTML, "#sItem#", trim(sItem)) 
            set .onmouseover = getRef("SubMenuOver") 
            set .onmouseout = getRef("SubMenuOut") 
            set .onclick = getRef("SubMenuClick") 
            set .onselectstart = getRef("cancelEvent") 
          end with ' child node 
          set oEL = document.createElement("BR") 
          .appendChild(oEL) 
        next 
      end with ' dropmenu 
    end if 
  end with ' srcEement 
 
end sub 
 
sub cancelEvent 
  window.event.returnValue = false 
end sub ' cancelEvent 
 
sub clearmenu 
 
  dropmenu.innerHTML = "" 
  dropmenu.style.border = "none" 
  dropmenu.style.backgroundcolor = "transparent" 
  if sMenuOpen <> "" then 
    document.getElementByID(sMenuOpen).style.border = "none" 
    sMenuOpen = "" 
  end if 
end sub 
 
' ###################  IMPORTANT  ################### 
' Code to accomplish each submenu item defined above 
' Can be as simple as a subroutine call 
' 
Sub SubMenuClick 
 
  sItem = trim(window.event.srcElement.innerText) 
  clearmenu 
  Select Case lcase(sItem) 
    case "open" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "close" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "save" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "save as ..." 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "exit" 
      window.close 
    case "cut" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "copy" 
    case "paste" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "select all" 
      msgbox "Sorry, " & sItem & " is not implemented" 
    case "deselect all" 
      msgbox "Sorry, " & sItem & " is not implemented"     
    case "help" 
      msgbox "Help under construction", vbOKOnly + vbInformation, "Help" 
    case "about" 
      msgbox "Copyright, 2013" & vbCRLF & "Chris wright"_ 
              & vbCRLF & "Released for Purchasing Only",_ 
              vbOKOnly + vbInformation, "About Menu" 
    case else ' catch all for undefined menu items 
      msgbox "Sorry, " & sItem & " is not implemented" 
  end Select 
 
end sub 
 
Sub RunScript
    Location.Reload(True)
End Sub

Sub LoadDropDown
	Dim Rs, cn, objOption 
  
	Set Rs = CreateObject("ADODB.Recordset")
	Set cn = CreateObject("ADODB.connection")
	cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dataSrc & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"  'Persist Security Info=False;"
	cn.Open
	Rs.Open "Select Distinct [Vendor Name] from [VPN Position$]", cn, 1, 3

	' add a blank
	Set objOption = Document.createElement("OPTION")
	objOption.Text = ""
	objOption.Value = -1
	OptionChooser.Add(objOption)

    Do Until rs.EOF
	Set objOption = Document.createElement("OPTION")
	objOption.Text = rs.fields("Vendor Name")
	objOption.Value = rs.fields("Vendor Name")
	OptionChooser.Add(objOption)
	rs.MoveNext
    Loop 

End Sub

Sub ClearListbox
    For Each objOption in OptionChooser.Options
       objOption.RemoveNode
    Next
End Sub

Open in new window

Batch Script (Named: batch.vbs)

Sub BatchRun
  ' I Uncommented the next line to ignore any issues that arose because it prevents error messages from being displayed
    'On Error Resume Next
	
  Dim WshShell
  Set WshShell = CreateObject("WScript.Shell")
	
  Dim cmd1
  cmd1 = "C:\oracle\discoverer\bin\dis51usr.exe /connect ""crwright:ADS Parts Manager/PASSWORD@prod"" /opendb ""ADS Stocking Position By Vendor"" /parameter ""Supplier_Name""  echo %1  /sheet ALL /export XLS ""\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.xls"" /batch"
	
  ' run and hide the window and wait for execution to finish
  WshShell.Run cmd1, 1, True


  ' I run this code if I wanted to email the formatted workbook
  	
  Dim cmd2
  cmd2 = "I:\Chris Wright\Chris Wright\Daily Projects\Miscellaneous\Daily Macros\Daily Position Macro-Working File.xlsm"
	
  ' run and hide the window and wait for the macro to close the spreadsheet
  WshShell.Run cmd2, 1, True
	
  Set WshShell = Nothing

End Sub

Open in new window

Original Batch Script (Named: batch.bat)
SEE SNIPPET IN CID: 39288378

Last Snippet removed per request MIT 7-1-13

if not "%minimized%"=="" goto :minimized
   set minimized=true
   start /min cmd /C "%~dpnx0"
   goto :EOF
   :minimized
   rem runs the script in a minimized window


C:\oracle\bin\dis51usr.exe /connect "crwright:ADS Parts Manager/cew5310@PROD" /opendb "ADS Stocking Position Report" /sheet ALL /export HTML "\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.html" /batch

Open in new window

Christopher Wright

ASKER

Hi Rob, I noticed that I did not address some of your questions in Thread ID: 39286472. I have addressed them below. Sorry about that. :(

I can't see any of your VBS or HTA code, so I'm not sure at what point the Discoverer program is initiated to export to XLS.

- Currently, the Discoverer program has been designed to be initiated when the 'Batch' button is clicked. However, I have failed to ever get it to successfully run. I guess I may have got a little ahead of myself when I started to talk about having the Discoverer program feed the dropdown in the HTA. First things first, I should be worrying about having the Discoverer report successfully run when the button is clicked.

I'm also not sure what the drop down selection is built from in the HTA. My assumption is that the Discoverer program is being called via a batch command (that can be transferred to run from the HTA) that is exporting to XLS. How would you go about making Discoverer export to HTML?

-The drop down selection is built from an XLS file that has been previously ran and exported via the 'Original Batch Script'. This Batch is run with Windows Task Scheduler at midnight every night. The Discoverer Report has the functionality to export to HTM just as it exports to XLS. If I were to change the 'Original Batch Script' to the code snippet provided below, it would export to an HTM file in the same location. Just as you mentioned in the quote:

I was more thinking of it from a point of view where you would export the data to HTML, and then just dislay the static HTML report as it was exported (which is what the second objShell.Run statement would do).

SEE SNIPPET IN CID: 39288378
Snippet removed per request MIT 7-1-13

Obviously XLS and HTML are completely different formats, so if you HTA is built to present a custom interface to that data, then you would have to rewrite the code to still present the drop down options from the raw HTML data.....I'm not sure if this would save you any time.

-If it would not save time, then what would be a faster alternative?

if not "%minimized%"=="" goto :minimized
   set minimized=true
   start /min cmd /C "%~dpnx0"
   goto :EOF
   :minimized
   rem runs the script in a minimized window


C:\oracle\bin\dis51usr.exe /connect "crwright:ADS Parts Manager/cew5310@PROD" /opendb "ADS Stocking Position Report" /sheet ALL /export HTML "\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.html" /batch

Open in new window

Christopher Wright

ASKER

Also, just another heads up Rob; the 'Original Batch Script' in Thread ID: 39287373 SHOULE BE the below snippet:

 if not "%minimized%"=="" goto :minimized
   set minimized=true
   start /min cmd /C "%~dpnx0"
   goto :EOF
   :minimized
   rem runs the script in a minimized window


C:\oracle\bin\dis51usr.exe /connect "crwright:ADS Parts Manager/PASSWORD@PROD" /opendb "ADS Stocking Position Report" /sheet ALL /export HTML "\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.html" /batch

Open in new window

You should be able to package code into a .VBS file that you can execute from a batch file (.Bat or .Cmd) or from an .HTA applet.

.HTA files usually interact with the user. For the purposes of invoking the extract program and then handling the output, you really shouldn't have any user interaction.

Christopher Wright

ASKER

Thanks aikmark. I have done that as well. If you look just above my Original Batch Script, I embedded the VBS version of the Batch. I only provided the Bat file for reference. Thanks

@xfitguru

I do not have the ability to test any of your scripts or validate my assertion that you should be able to bypass the Excel step (if I've read the question correctly).

Christopher Wright

ASKER

Okay. I was only reaching out to see if anyone had ideas or maybe encountered this situation before.

having worked on your other questions about this I would recommend exporting to HTML and remove the Excel from the equation completely. The HTML can then be displayed in the HTA

>> The HTML can then be displayed in the HTA

I was thinking along those lines too, tagit, but opted to have IE display the HTML as my first code snippet shows, but I wasn't sure whether other functions were being performed on the Excel data to present a different user interface. I haven't had time to look through the code yet, but hope to by the end of the week.

Rob.

No worries Rob.

Christopher Wright

ASKER

Thanks for looking into this Gentlemen!! I sincerely appreciate it!

Christopher Wright

ASKER

Hi Rob. Any luck with this one yet? Thanks

Christopher Wright

ASKER

Bump - Anyone able to help??? Thanks

My apologies. I'll try to have a look this morning...

Hi, after an initial look at the HTA, you are providing what is basically a front end search facility to the data that is exported from the database. Since your front end is not querying the Oracle database directly, you rely on querying the exported data. Excel is actually the best "offline" format for the data, since it supports SQL queries via the OLE connection you are using, allowing you to quickly filter your results.

If you were to export to HTML instead, and still wanted a search functionality, you would basically have to parse the HTML data, read it into an offline recordset anyway (using the ADOR.Recordset object: http://www.w3schools.com/ado/ado_ref_recordset.asp), and then still query that using SQL queries.

The only way to otherwise streamline the process would be to query the Oracle database directly, using a connection string like this:
http://connectionstrings.com/oracle#microsoft-ole-db-provider-for-oracle-msdaora

This would allow you to not utilitise data export, and also have live data at each query. Of course it may slow the process down, but it shouldn't be by too much.

Let me know if I'm on the right track there.....but I think the bottom line is that an HTML export is actually going to be more complicated than the XLS export, unless you only want to display the HTML page as it is.

Regards,

Rob.

As I understood it the HTML is a formatted report from discoverer? If not then it would certainly be worth looking at direct querying of the database as Rob has so well described.
It really depends on the complexity of the reports and if discoverer is warehousing the data or other BI data manipulations

Christopher Wright

ASKER

Hello Rob. You are on the right track with this. While I am definitely interested in querying the database directly, I am forced to settle with querying via Oracle Discover 10g for now. As you already aware, my HTA is querying an Excel spreadsheet that is exported every night with the Oracle Discoverer program. I simply have used Windows Task Scheduler that prompts a DOS Batch script to call the program, query the data, and export to an XLS. However, this same Discoverer program has the capability to export to an HTM file format just as it is exporting to an XLS format now. I am not versed enough to make this happen though.

So essentially, we could have the Discoverer program running in the background in a hidden state, then have the data queried to feed the table located in the HTA. Is it possible to have this HTA could fed directly from the exported HTM file created by the Discoverer program? This would essentially be live data, but instead of coming directly from the database, it is coming from the Discoverer program. We could enable the query to be refreshed via a refresh button that would refresh the Discoverer info as well. Is this possible/feasible? Or is it not practical? Thank you for your time guys!!!

This really depends on how up to date you need the data and what would be acceptable. At the last place I worked they only needed the data updated once a day so more often than not people don't need "live" data unless their going to be making decisions on it every minute of every day.
I would stick with the Excel output for now and continue querying it from the HTA. It is a lot easier to query than trying to parse the HTML for no benefit.
I'm guessing that as long as discoverer is run say every hour via Task Scheduler then your Excel spreadsheet would be "up to date".
As for having a refresh button, I wouldn't put it in. See how your clients go with data an hour (at most 2 hrs) old.

Christopher Wright

ASKER

Roger that tagit. I am going to continue that practice for now. I was only interested in the HTM output for a couple of reports that they would need live data every minute and constantly updated for the most up to date data.

If that's really is the case then I would run the task every minute or less to ensure it's live. How much overhead and interference does the task impose? Are you running it on a standalone computer or are you running it on your local machine where it would pop up all the time?

From what I can gather in your code, you are presenting the user with a list of vendor names (from the XLS file) to report on. Based on the user selection in the HTA, you show a report of some data, that is obviously static, and only as new as the spreadsheet itself.

At this point, I also notice you run this line at one point:

  cmd1 = "C:\oracle\discoverer\bin\dis51usr.exe /connect ""crwright:ADS Parts Manager/PASSWORD@prod"" /opendb ""ADS Stocking Position By Vendor"" /parameter ""Supplier_Name""  echo %1  /sheet ALL /export XLS ""\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.xls"" /batch"

Open in new window

What I notice here is that you are able to supply parameters to the query. So, what I'm thinking is that once the user clicks Search to build the report, that you run something like

  cmd1 = "C:\oracle\discoverer\bin\dis51usr.exe /connect ""crwright:ADS Parts Manager/PASSWORD@prod"" /opendb ""ADS Stocking Position By Vendor"" /parameter ""Supplier_Name""  " & optionchooser.Value & "  /export HTML ""\\ads-fs1\ads\Chris Wright\Chris Wright\Daily Reports\Daily Stocking Position.html"" /batch

Open in new window

and then use objShell.Run to open that generated report, or, use the FileSystemObject to read in that HTML data, and display in a SPAN element within the HTA.

This way, you may be able to take advantage of both approaches, where the XLS provides the Vendor Names on a daily basis, and the HTA then fires a targeted query to pull the report for the selected vendor, in real time. This targeted query would hopefully be much faster than an entire HTML report, and also be targeted to be more relevant to the user anyway.

If that does work, then depending on your data requirement, you may even be able to shorten the XLS scheduled task to only pull Vendor Names daily, since that's the only static data you need. I'm not really show how it works, but that theory seems like it might be doable.

I hope I haven't complicated the whole thing though ;-)

Regards,

Rob.

No, I think you're on the right track Rob. That would ideally be the best solution to be able to pass the parameters right to the query and remove the need for excel altogether.

I think so. One thing we don't know yet is how long the task takes to dump all of the data to the XLS. I think though that you'll still need the initial XLS since its easier to query for the option boxes, but after that if you just want a static report, then send a parametised query to output to HTML and display that. If the initial XLS task takes ages, then try to filter that query as well to make it faster, since you only use one or two sheets of it in the HTA data.

Hope that helps.

Rob

Yes that's a good point that we need to know how long Discoverer typically takes to run at this point to recommend a course of action.

Does the time taken differ when exporting to XLS vs HTML?

Does the time taken differ much between exporting the entire report vs providing parameters? My thoughts with this are the report structure has already been created and is Discoverer just filtering the dataset (as if you were doing it in Excel) or is it passing the parameter through to the query on the database...

Christopher Wright

ASKER

Hi tagit. I have provided an answer to your questions below:

Does the time taken differ when exporting to XLS vs HTML?

- Yes. The time does differ. I have provided how long it takes for each below:

- XLS: 5.20
- HTML: 3.25

Does the time taken differ much between exporting the entire report vs providing parameters?

- Yes. The time does differ. It takes a considerable amount of time to run the entire report -vs- running the the report with parameters since the report with parameters is pulling data based on the selected vendors while the entire report pulls data for every vendor.

Thanks that's really helpful.

it shows that it doesn't matter whether exporting to html or xls though please clarify if that is minutes or seconds.

Also shows that it is worth providing parameters assuming the report can be run within an acceptable amount of time.

I'll need you to clarify seconds or minutes but also how long it takes to run the report either providing paramters AND without. Your best guess is fine.

To me, the time makes all the difference here because if it takes even 1 minute to run the parametized report then to most of your clients I would speculate would think that's too long to wait. As you said before they are expecting "live" data (or as close as you can get). If that's the case then having reports run in the background that can be viewed via your HTA quickly may be your best bet. Won't know until I have the stats from the previous post.

Christopher Wright

ASKER

Sorry for that. In Thread ID: 39327090, the data is in minutes.

- XLS: 5 minutes 20 seconds
- HTML: 3 minutes 25 seconds

Ok great and what about running the parameter based report?

I dare say it's going to take a while to run but still have it available on your form as an option for your users.
This is how is approach this now I know the timings:
Export to excel every ten minutes given it takes 5 minutes to run. Then query the xls from your form. That is for the full data right?
If the user wants a personalized report, run the parameterized version but have it export to html and bring it straight into your hta.
Sound ok?
.

Christopher Wright

ASKER

Those times pertain to the parameter based report.

Christopher Wright

ASKER

Tagit, I like your idea. I say that would be best approach for this. What is the first step?

I would go with tagit's approach, except that I would question the need to export the data to XLS so frequently. As I mentioned, since it only appears that you are pulling Vendor Names from the XLS to present to the users via the HTA, do you need vendor names to be updated "live"? If you only add vendor names on occasion to the database, then this would still be OK daily, I would imagine.

Then, once the user selects a vendor in the HTA, run the parametized version via dis51usr.exe and read that into the HTA. I would try to add as many parameters as possible to narrow down the query, for two reasons. One, it makes the report more relevant to the user, and two, it would make it faster as well.

Regards,

Rob.

From what I've seen in the XLS there seems to be more than just vendor names in there? Can you clarify?

First thing to do is to keep your existing export of data but just change from once a day to once every 10 minutes.

The idea is to have the XLS with 99% of the data people will query. That way everyone gets their data "realtime".

So secondly, for the paramertised report (as been mentioned above), make these parameters available on your HTA and use them in the batch that to run Discoverer but this will output to HTML that you can bring into the HTA as Rob has mentioned above in one of his posts.

If you find that most of your users are using the parametised report and complaining about waiting then try and include that data into your export to XLS. It would be meant for that uncommon report that may only be needed in certain circumstatances or not all the time.

Remember you also don't have to do this all in one HTA. I found from experience it was easier and clearer to the users breaking up the front end interfaces into logical groups such as Sales, Inventory and Customer Records. The users then opened the relevant one. If you do this then you can also split the XLS to multiple versions related to each one.

Christopher Wright

ASKER

Awesome! Thanks Rob and Thanks Tagit! You guys clearly live up to the title of EXPERT! One question though. Tagit, when you state

So secondly, for the parameterized report (as been mentioned above), make these parameters available on your HTA and use them in the batch that to run Discoverer but this will output to HTML that you can bring into the HTA as Rob has mentioned above in one of his posts.

I do not know how to do this. To my shame since I am still but a newbie. Secondly, I am really intrigued with your approach. I love it! I would definitely need assistance making that a reality. Could you help? I am going to mark this as answered and create a new question pertaining to having the parameters in HTA. Once that comes to fruition, I will submit another question pertaining to breaking up the front end interfaces. Thanks again!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christopher Wright

ASKER

Absolutely phenomenal support and training here. Thank you so much for the help Rob and tagit. I have marked this thread as solved and created another question that pertains to the User Interface development.

Rob,
I like the example you provided. Very nifty!

Tagit,
I totally understand what you mean when you state, "Remember that if you give an inch your users will take a mile." This is very true

I have posted the new question. The link is below. Again, thank you SO MUCH for the help!!

https://www.experts-exchange.com/questions/28195350/Creating-a-User-Interface-to-Pull-Access-Multiple-Reports.html