HTA Spreadsheet - Spawn and populate?

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

HTA Spreadsheet - Spawn and populate?

Logged in as: Guest

arrSession:exec spGetSession 2,2,50567

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> HTA Spreadsheet - Spawn and populate?
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

HTA Spreadsheet - Spawn and populate? - 8/10/2007 1:40:13 AM

Brad

Posts: 13
Score: 0
Joined: 8/10/2007
Status: offline

Basically, I am writing an HTA that pulls some info from a database, calculates statistics, and spits out the info in spreadsheet and chart form.

The rub is that the formatting for the spreadsheet and chart needs to be different depending on which button the user clicks. It's very easy to set up the <object> then re-populate the information with a button click. The problem is when that button needs to be used to onclick= run the sub that spawns the spreadsheet object itself, but ALSO run the sub that populates the sheet.

You can't do it inside the SpawnSpreadsheet sub anywhere, because we have to get kicked back to parse the HTML that creates the spreadsheet object first.

MEH!!!#!$#$%

Any help would be appreciated. :)

-Brad

< Message edited by Brad -- 8/10/2007 1:42:00 AM >

Post #: 1

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 1:51:12 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

The special function used to load stuff when the HTA starts is REALLY cool!

Function window_onload

Ack, Ack, Ack, Ack, Ack, Ack (your stuff) ;-)

End Function

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to Brad)

Post #: 2

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:01:35 AM

Brad

Posts: 13
Score: 0
Joined: 8/10/2007
Status: offline

Smarta$$. :)

That works just fine, if I want a blank spreadsheet to pop up on the screen as soon as the HTA is loaded.

To make a long story short, let's say I want nothing but a button on the screen. When I click the button, I want a fully-populated and properly formatted spreadsheet to appear in a <span>. Right now, I have two options to run this thing:

1) show the user a blank spreadsheet, populate it on the click of a button. (works, but looks Busch League.)

2) don't show the user a blank spreadsheet, spawn a blank spreadsheet on the click of a button, populate on the click of another button. (pointless)

...but I need option 3.

3) don't show the user a blank spreadsheet, spawn AND populate on the click of a button.

The catch-22 is that the object has to exist for it to be populated, but the object has to be displayed blank on the screen for it to exist. It would be nice if I could invisibly create the object, populate it, then bring it into view.

Is it possible? Thanks!

-Brad

(in reply to mcds99)

Post #: 3

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:06:57 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Couldn't you use the CSS style display = none on the span prior to the populated display?

(in reply to Brad)

Post #: 4

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:25:03 AM

mcds99

Posts: 441
Score: 4
Joined: 2/28/2006
Status: offline

As far as CSS goes, I'm lost.

I would use the onload to populate the spreadsheet non-interactive.
Loop through writing the rows and columns

   objSheet.Cells(row, 1).value = ClmA
objSheet.Cells(row, 2).value = ClmB
      etc...
Then save it...
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs XlsPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
     row = row + 1
And so on...

Then the button to display it.

*** Where is the data coming from, a database?

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to ehvbs)

Post #: 5

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:34:12 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Perhaps I'm on the wrong track, but try:

<html>
<head>
 <hta:application id = "excel2"
 />
 <title>excel II</title>
 <meta http-equiv = "content-script-type" content = "text/vbscript"/>
 <script language = "VBScript"
        type     = "text/vbscript"
 >
'<![CDATA[

''= populates the spreadsheet and makes the containing div visible
' ============================================================================
Sub populateExcel()
 Spreadsheet1.TitleBar.Caption = "Caption"
 Spreadsheet1.Range("A1").Cells(1) = "filled by yadda yadda yadda"
 document.getElementById( "divExcel" ).style.display = "block"
End Sub

''= refreshes the HTA page, which includes re-running any Windows_Onload code
' ============================================================================
Sub reloadHTA()
 location.reload( True )
End Sub

']]>
 </script>
</head>
<body>
 <input type = "BUTTON" value = "reload" onclick = "reloadHTA()">
 <hr />
 <input type = "BUTTON" value = "populate" onclick = "populateExcel">
 <hr />
 <div id = "divExcel" style = "display:none">
 <object classid = "clsid:0002E510-0000-0000-C000-000000000046"
         id      = "Spreadsheet1"
         width   = "90%"
         height  = "30%"
 >
   <param name="DisplayToolbar" value="0">
   <param name="DisplayWorkbookTabs" value="0">
   <param name="DisplayTitleBar" value="-1">
 </object>
 </div>
</body>
</html>

added after reading your message:

kind of cross post - the idea is to 'create' the object in an invisible container (I used a div)
and show it after the populating.

< Message edited by ehvbs -- 8/10/2007 2:40:24 AM >

(in reply to mcds99)

Post #: 6

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:34:37 AM

Brad

Posts: 13
Score: 0
Joined: 8/10/2007
Status: offline

It sounds like that may be on the right track with what I am trying to do, but I don't quite follow...

True, that does hide the spreadsheet after it's been spawned by the button click, but it still won't be populated... I'm sorry, I haven't had coffee yet this morning. :) Could you give me a little example of how this will help?

*EDIT* - Just saw your last posts... What in that code will bring the spreadsheet into view? It appears that it will always be hidden...

*EDIT EDIT* - Ok, saw your edit. (lol) - That makes sense to me. I will try it out!

-Brad

< Message edited by Brad -- 8/10/2007 2:41:57 AM >

(in reply to ehvbs)

Post #: 7

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:40:04 AM

Brad

Posts: 13
Score: 0
Joined: 8/10/2007
Status: offline

Yes, it's coming from a DB. The problem is that when the onload function is executed, the spreadsheet object doesn't exist yet so I can't populate it. It is not (and cannot be) created until the user clicks a button, which throws the <object> creation into a <span>.

quote:

ORIGINAL: mcds99

As far as CSS goes, I'm lost.

I would use the onload to populate the spreadsheet non-interactive.
Loop through writing the rows and columns

objSheet.Cells(row, 1).value = ClmA
objSheet.Cells(row, 2).value = ClmB
etc...
Then save it...
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs XlsPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
row = row + 1
And so on...

Then the button to display it.

*** Where is the data coming from, a database?

(in reply to mcds99)

Post #: 8

RE: HTA Spreadsheet - Spawn and populate? - 8/10/2007 2:43:48 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

To avoid further ping pong, I won't edit again, but use a new posting:

This line

document.getElementById( "divExcel" ).style.display = "block"

will make the spreadsheet visible.

(in reply to Brad)

Post #: 9

RE: HTA Spreadsheet - Spawn and populate? - 8/13/2007 4:09:09 AM

Brad

Posts: 13
Score: 0
Joined: 8/10/2007
Status: offline

I just wanted to let you guys know that ehvbs's solution worked perfectly! Thanks!

-Brad

(in reply to Brad)

Post #: 10

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> HTA Spreadsheet - Spawn and populate?

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts