# 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
Director, Service DeliveryAsked:
###### Who is Participating?

Owner (Aidellio)Commented:
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

Commented:
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


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

Regards,

Rob.
0

Director, Service DeliveryAuthor Commented:
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

0

Commented:
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

Director, Service DeliveryAuthor Commented:
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

Director, Service DeliveryAuthor Commented:
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>

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;
}

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 '============================================  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

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

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

0

Director, Service DeliveryAuthor Commented:
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

0

Director, Service DeliveryAuthor Commented:
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

0

Commented:
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

Director, Service DeliveryAuthor Commented:
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

Commented:
@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

Director, Service DeliveryAuthor Commented:
Okay.  I was only reaching out to see if anyone had ideas or maybe encountered this situation before.
0

Owner (Aidellio)Commented:
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

Commented:
>>  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

Owner (Aidellio)Commented:
No worries Rob.
0

Director, Service DeliveryAuthor Commented:
Thanks for looking into this Gentlemen!!  I sincerely appreciate it!
0

Director, Service DeliveryAuthor Commented:
Hi Rob.  Any luck with this one yet?  Thanks
0

Director, Service DeliveryAuthor Commented:
Bump - Anyone able to help??? Thanks
0

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

Commented:
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

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
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

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
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

Owner (Aidellio)Commented:
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

Commented:
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"


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


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

Owner (Aidellio)Commented:
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

Commented:
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

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
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

Owner (Aidellio)Commented:
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

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
Sorry for that.  In Thread ID: 39327090, the data is in minutes.

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

Owner (Aidellio)Commented:
Ok great and what about running the parameter based report?
0

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
Those times pertain to the parameter based report.
0

Director, Service DeliveryAuthor Commented:
Tagit, I like your idea.  I say that would be best approach for this.  What is the first step?
0

Commented:
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

Owner (Aidellio)Commented:
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

Director, Service DeliveryAuthor Commented:
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

Commented:
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

Director, Service DeliveryAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.