Hey, Scripting Guy! How can I put a button in an HTA that, when clicked, exports an HTML table to Excel?
— DW
Hey, DW. You know, yesterday afternoon the Scripting Guy who writes this column went to get his hair cut, figuring he needs to look his best for TechEd 2007. (It was only after he got his hair cut that he realized what a futile gesture that was; he should have stopped by the plastic surgeon’s rather than the barbershop.) As he was parking his car, he noticed the following sign in a restaurant window:
SOUP-FREE
This sign intrigued the Scripting Guy who writes this column to no end. His first thought, of course, was that soup was free at this restaurant: order a bowl of soup and they won’t charge you for it. But then he thought, “Maybe this is like a smoke-free restaurant, except that it’s soup-free; maybe you can smoke in there but you can’t order a bowl of soup.” For the Seattle area, that seemed plausible:
“Where do you want to eat tonight?”
“How about that new place in Redmond?”
“Do they serve soup there?”
“I think so.”
“Then forget it; I will never set foot in any restaurant that serves soup! We either find a soup-free restaurant or we stay home.”
At any rate, if anyone out there is looking for a soup-free restaurant, well, just let us know; we think we have one for you.
|
Note. As intriguing as the SOUP-FREE sign is, it still ranks as only the second-most intriguing sign the Scripting Guy who writes this column has ever seen. But, then again, it’s going to be hard to top the Certificate of Non-Significance. And no, the Scripting Guy who writes this column doesn’t actually have a Certificate of Non-Significance. Some things are so obvious they don’t need any kind of formal certification. |
If it turns out that you are violently opposed to the whole idea of soup, well, we have good news for you: today’s solution is also 100% soup-free. The truth is, we didn’t know of any simple and straightforward way to export an HTML table to Excel (with or without soup). However, we came up with something we thought might be even better: a simple and straightforward way to copy an HTML table and then paste it into Excel. In fact, we came up with this way to copy an HTML table and then paste it into Excel:
<Script Language=”VBScript”>
Sub RunScript
strCopy = MyTable.InnerHTML
document.parentwindow.clipboardData.SetData “text”, strCopy
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Paste
End Sub
</Script>
<body>
<span id=MyTable>
<table border=”1″ width=”100%” id=”table1″>
<tr>
<td>Cell 1</td>
<td>Cell 2</td>
<td>Cell 3</td>
</tr>
<tr>
<td>Cell 4</td>
<td>Cell 5</td>
<td>Cell 6</td>
</tr>
<tr>
<td>Cell 7</td>
<td>Cell 8</td>
<td>Cell 9</td>
</tr>
</table>
</span>
<br>
<input id=runbutton type=”button” value=”Run Button” onClick=”RunScript”>
</body>
What we have here is a very simple little HTA, one that looks a lot like this:
As you can see, the bulk of our little HTA is a table, in this case a nine-cell table (three rows by three columns). Per DW’s email, we’ve put this table inside a <SPAN> tag with the ID MyTable; here’s the code that creates the <SPAN> and initializes the table:
<span id=MyTable>
<table border=”1″ width=”100%” id=”table1″>
For those of you new to HTML tagging, a <SPAN> is simply a named area of your document; we can define an area of the document and then stuff that area full of anything we want (in this case, the only thing we’ve put in the <SPAN> is a table.) The cool part, however, is this: as long as we give the <SPAN> an ID we can then manipulate that portion of the document programmatically. That might mean making dynamic changes to that section of the HTA. Or, as is the case here, it might be doing nothing more than grabbing all the information inside the <SPAN>.
The long and short of it? We have a table in our HTA and we have a mechanism for identifying and manipulating that table.
We only have one other element in the HTA body: a button (labeled Run Button) that, when clicked, runs a subroutine named RunScript:
<input id=runbutton type=”button” value=”Run Button” onClick=”RunScript”>
As you might expect, RunScript is the subroutine that’s going to grab a copy of the table and somehow get that copy into an Excel spreadsheet.
Good question: how does this subroutine grab a copy of the table and get that copy into an Excel spreadsheet? Let’s see if we can figure that out.
To begin with, we use this line of code to connect to the MyTable <SPAN> and assign the value of the InnerHTML property to a variable named strCopy:
strCopy = MyTable.InnerHTML
What’s the InnerHTML property? That’s basically everything that falls between the beginning <SPAN> tag and the ending </SPAN> tag; that includes both the data found in the table and the HTML tagging for creating and formatting that table. In other words, we now have all the information necessary for re-creating the table stashed away in the variable strCopy. Once that’s done we use this line of code to copy that information to the Windows Clipboard:
document.parentwindow.clipboardData.SetData “text”, strCopy
What we’re doing here is calling the SetData method, a method that just happens to belong to the clipboardData object. In addition, we’re passing SetData two parameters:
| • |
“text”, which represents the type of data being copied to the Clipboard. |
| • |
strCopy, the actual data we want to copy to the Clipboard. |
As you probably guessed, after calling SetData our table will be copied to the Windows Clipboard; in turn, that means we’re ready to paste this data into Excel. To that end, we first create an instance of the Excel.Application object and then set the Visible property to True:
Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True
That gives us a running instance of Excel that we can view onscreen. We then take that running instance, create a new workbook, and bind to the first worksheet in that workbook:
Set objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1)
So then how do we paste in our table? Why, by calling the Paste method, of course:
objWorksheet.Paste
Yes, just like a soup-free restaurant, this sounds too good to be true. But give it a try and see for yourself:
Truth be told, this is pretty darn easy; play around with this a little bit and you’ll quickly catch on. And you might have to play around with it by yourself because, for better or worse, we have to call it a day; after all, we need to create a new sign for the Hey, Scripting Guy! column:
SCRIPT-FREE
So does that mean that all the scripts in Hey, Scripting Guy! are free of charge … or does that mean that Hey, Scripting Guy! doesn’t even try to include script code any more? Good question. (Although it is getting harder and harder for us to find room for script code, what with the need to talk about haircuts, soup-free restaurants, and Certificates of Non-Significance.)
0 comments