Solved

Run Batch with HTA Button

Posted on 2013-06-27
43
2,401 Views
Last Modified: 2013-07-25
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
0
Comment
Question by:Christopher Wright
  • 18
  • 13
  • 9
  • +1
43 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 39283378
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.
0
 

Author Comment

by:Christopher Wright
ID: 39284839
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

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39286472
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.
0
 

Author Comment

by:Christopher Wright
ID: 39286914
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!!
0
 

Author Comment

by:Christopher Wright
ID: 39287373
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

0
 

Author Comment

by:Christopher Wright
ID: 39288375
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

0
 

Author Comment

by:Christopher Wright
ID: 39288378
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

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39288582
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.
0
 

Author Comment

by:Christopher Wright
ID: 39291310
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39291335
@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).
0
 

Author Comment

by:Christopher Wright
ID: 39291350
Okay.  I was only reaching out to see if anyone had ideas or maybe encountered this situation before.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39295454
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
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39295596
>>  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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39295612
No worries Rob.
0
 

Author Comment

by:Christopher Wright
ID: 39296422
Thanks for looking into this Gentlemen!!  I sincerely appreciate it!
0
 

Author Comment

by:Christopher Wright
ID: 39304407
Hi Rob.  Any luck with this one yet?  Thanks
0
 

Author Comment

by:Christopher Wright
ID: 39307497
Bump - Anyone able to help??? Thanks
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39308920
My apologies.  I'll try to have a look this morning...
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39308968
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39309324
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
0
 

Author Comment

by:Christopher Wright
ID: 39322294
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!!!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39322423
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.
0
 

Author Comment

by:Christopher Wright
ID: 39322714
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39322723
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?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39323141
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39323261
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.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39324211
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
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39324228
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...
0
 

Author Comment

by:Christopher Wright
ID: 39327090
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39332151
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39332155
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.
0
 

Author Comment

by:Christopher Wright
ID: 39334668
Sorry for that.  In Thread ID: 39327090, the data is in minutes.

   - XLS: 5 minutes 20 seconds
   - HTML: 3 minutes 25 seconds
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39335199
Ok great and what about running the parameter based report?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39335206
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?
.
0
 

Author Comment

by:Christopher Wright
ID: 39340868
Those times pertain to the parameter based report.
0
 

Author Comment

by:Christopher Wright
ID: 39340869
Tagit, I like your idea.  I say that would be best approach for this.  What is the first step?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39341088
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.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39343448
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.
0
 

Author Comment

by:Christopher Wright
ID: 39349085
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!
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 250 total points
ID: 39349685
We can help you with that, although it does make it very difficult when we can't see the interface or end result.  Perhaps it would help if you posted a few screenshots of how the workflow goes.

Basically though, to add parameters to the HTA, you would add text boxes, list boxes, or radio options that the user could select, then the HTA would run the Discoverer command incorporating those selections as I showed in comment ID a39323141

You see there that I throw in the variable value given by th optionchooser list box.

As a basic example of an application that uses user selections as command parameters, look at the RichCopy GUI:
http://technet.microsoft.com/en-au/magazine/2009.04.utilityspotlight.aspx

With that application, the user selects a bunch of options, and the utility passes those to the command line to run the RichCopy application.  Your HTA would do a similar thing.

Regards,

Rob.
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 250 total points
ID: 39349944
And Rob has pointed out in one of his earlier posts about how to call the discoverer program with parameters.
There will be a few ways to approach this, one being the way I mentioned above but only you will ultimately know which way to go given your working environment and the requirements of people that work there.
What are the requirements of the parametised version?
What are your users going to want to look for?
If this were me, my ultimate goal would be to have the search functionality as you have now and no discoverer but instead a standard list of already generated reports that they can select that will show instantly.  they will have been generated in the background just like the xls.
 However you won't know if that suits until you go through the process of the parametised version.

So fist steps are to work out what those parameters might be. Remember that if you give an inch your users will take a mile so keep them as brief as possible and ask them to work out exactly what they're after that they can't get from the search.  Once we know what they are we can suggest how to organise them on the hta
0
 

Author Closing Comment

by:Christopher Wright
ID: 39355615
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!!

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28195350.html
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
This article discusses how to create an extensible mechanism for linked drop downs.
In this tutorial viewers will learn how add a scalable full-width header using CSS3. Create a new HTML document with an internal stylesheet. Set a tiled background.:  Create a new div and name it Header. Position it with position:absolute at the top…
In this tutorial viewers will learn how to code links for mobile sites that, once clicked, send a call or text to a specified number. For a telephone link (once clicked, calls a number), begin with a normal "<a href=" link tag. For the href, specify…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now