WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with MSIE
plus
User Samples

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Display data in a Columnar or Grid format

Question:

Is there anyway in WinBatch to display data (or an array) in a columnar or grid like format. I have created a WBT file that queries a SQL Server database for active users. I need the feedback to the user to display the UserId, Name, IP Address, etc... on each record pulled from the database. I would like a grid type of look and feel (I don't need to write back into the database).

I tried to simulate a grid using the strfix and a message box, but this yields inconsistent formating results. In addition, I can create an IE Application and bind the resultset data as HTMLtext to an OWC spreadsheet component. However, Winbatch is not passing the aposthrophe properly to the SS component, which the component needs to determine a numeric field (Otherwise, leading zeros are dropped in the SS). Any ideas?

Answer:


; this works with Windows XP Pro
; Microsoft Office Web Components 9.0 (MSOWC.DLL)
; MSIE 6.0+
;
; you will need to edit the wbtpath, otherwise it'll run by itself...
;

;   old script ignore the commercial

;   check down on line 133 or so .NumberFormat seems to work...

:startMSIE
Browser = objectopen("InternetExplorer.Application")
browser.visible = @true
;   don't worry about a non-existent URL...
;   once we start sending HTML to the .document it'll pop-up fresh...
url = "c:\AnyOldURL.html"
browser.navigate(url)

;   leave this next section commented out it'll go right to a blank document...
;while browser.readystate <> 4
;   timedelay(0.5)
;endwhile

;   setup the document object...
browserDoc = Browser.Document
;   write the VBScript subprogram...
gosub writescript
;   write the page title...
browserdoc.write('<title>Winbatch Test</title>')
;   write the hidden variable that will hold the value to tell if the button has been clicked...
gosub writehidden
;   send a message to the user...
browserdoc.write('<body bgcolor=black text=white><center><br><br><br><h1>')
browserdoc.write('Test Embedding of <font color=blue><i> MS Office Web Components </i></font>with Winbatch...')
browserdoc.write('</h1></center></body>')
;   write the CONTINUE button...
gosub writebutton
;   setup the form's objects for the loop test...
hform = browserdoc.hiddenform1
hfield = hform.hiddenfield
;   loop until the button is clicked...
while @true
   if hfield.value == "true" then break
   timedelay(0.5)
endwhile
;   remove the previous page's text and send next message...
browserdoc.close
;   write the page title...
browserdoc.write('<title>Winbatch Test</title>')
;   write the VBScript subprogram...
gosub writescript
;   write the hidden form...
gosub writehidden
;   display the next page's message...
browserdoc.write('<body bgcolor=blue text=white><center><br><h3>')
browserdoc.write('I was just experimenting with embedding an ACTIVE X object into MSIE, ')
browserdoc.write("I've seen it in various ASP/VBScript examples and figured it was possible ")
browserdoc.write("via Winbatch. Turns out it's a snap to access the embedded spreadsheet and ")
browserdoc.write('it functions <font color=red><i>almost</i></font> like Excel thru OLE.')
browserdoc.write('<br>')
browserdoc.write('The spreadsheet is re-sizeable and interactive...you can hit the toolbar buttons and ')
browserdoc.write('export it into Excel if you need to save it, as FILE/SAVE/COMPLETE PAGE only saves the HTML. ')
browserdoc.write('</h2></center></body>')
;   write the CONTINUE button...
gosub writebutton
;   setup the new document's form objects...
hform = browserdoc.hiddenform1
hfield = hform.hiddenfield
;   loop until the button is clicked...
while @true
   if hfield.value == "true" then break
   timedelay(0.5)
endwhile
;   close the previous text and display the spreadsheet...
browserdoc.close
;   write the page title...
browserdoc.write('<title>Winbatch Test</title>')
;   now embed the Spreadsheet Control into the document...
browserdoc.write('<center>')
browserdoc.write('<OBJECT ID="Spreadsheet1" WIDTH=720 HEIGHT=440')
browserdoc.write('  CLASSID="CLSID:0002E510-0000-0000-C000-000000000046"')
browserdoc.write('  STANDBY="Loading Spreadsheet..."')
browserdoc.write('  TYPE="application/x-oleobject">')
browserdoc.write('</OBJECT>')
browserdoc.write('</center>')
;   get a list of files...
wbtpath = "C:\z\"
wbtfiles = fileitemize(strcat(wbtpath, "*.wbt"))
;   setup the column...
y = 1
;   setup the Spreadsheet object for reference...
ss = browserdoc.Spreadsheet1
;  insert the header info...
thiscell = ss.cells(1,1)
thiscell.value = "File Name"
thisfont = thiscell.font
thisfont.bold = @true
thiscell = ss.cells(1,2)
thiscell.value = "File Size"
thisfont = thiscell.font
thisfont.bold = @true
;   insert values into the Spreadsheet...
for x = 1 to 10
;   insert the filename...
   thisfile = itemextract(x, wbtfiles, @tab)
   thiscell = ss.cells(x+1,y)
   thiscell.value = thisfile
;   insert the file size...
   thiscell = ss.cells(x+1,y+1)
   thiscell.value = filesize(strcat(wbtpath, thisfile))
next
;   jump down an extra row...
x = x + 2
;   reference the new cell and put the summary formula into it...
thiscell = ss.cells(x,y+1)
thiscell.formula = "=sum(B2:B12)"
;   re-size the first column...
thiscolumn = ss.columns(1)
;   strangely the size is very different...
;  I guess it uses MSIE units not Excel...
thiscolumn.ColumnWidth = 225

thiscolumn = ss.columns(2)
thiscolumn.NumberFormat = "#,##0.00"           ; <--- format with comma and leading zeros...

;   display the final message to the user...
browserdoc.write('<body bgcolor=tan text=red><center><br><br><h3>')
browserdoc.write('Fairly painless and it seemed like it was <br>')
browserdoc.write("<font color=black><u>easier</u></font> than automating Excel normally via OLE. ")
browserdoc.write('</h2></center></body>')
;   miller time...
exit

;   vbscript code html...
:writescript
browserdoc.write('<script language="vbscript">')
browserdoc.write(strcat('sub testscript', @crlf))
browserdoc.write(strcat('   document.hiddenform1.hiddenField.value = "true"', @crlf))
browserdoc.write(strcat('end sub', @crlf))
browserdoc.write('</script>')
return
;   hidden form's html...
:writehidden
browserdoc.write('<form name="hiddenform1" method="post" action="">')
browserdoc.write('  <input type="hidden" name="hiddenField" value="false">')
browserdoc.write('</form>')
return
;   continue button html...
:writebutton
browserdoc.write('<center><br><br><br>')
browserdoc.write('<form name="form1" method="post" action="">')
browserdoc.write('  <input type="submit" name="Submit" value="Continue" OnClick="testscript">')
browserdoc.write('</form></center>')
return


Article ID:   W16115

File Created: 2004:03:30:15:42:56
Last Updated: 2004:03:30:15:42:56