{"id":64783,"date":"2007-05-25T23:19:00","date_gmt":"2007-05-25T23:19:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/05\/25\/how-can-i-export-an-hta-table-to-excel\/"},"modified":"2007-05-25T23:19:00","modified_gmt":"2007-05-25T23:19:00","slug":"how-can-i-export-an-hta-table-to-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-export-an-hta-table-to-excel\/","title":{"rendered":"How Can I Export an HTA Table to Excel?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I put a button in an HTA that, when clicked, exports an HTML table to Excel?

— DW <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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<\/B><\/A>. (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\u2019s rather than the barbershop.) As he was parking his car, he noticed the following sign in a restaurant window:<\/P>\n

SOUP-FREE<\/P>\n

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\u2019t charge you for it. But then he thought, \u201cMaybe this is like a smoke-free restaurant, except that it\u2019s soup<\/I>-free; maybe you can smoke in there but you can\u2019t order a bowl of soup.\u201d For the Seattle area, that seemed plausible:<\/P>\n

\u201cWhere do you want to eat tonight?\u201d<\/P>\n

\u201cHow about that new place in Redmond?\u201d<\/P>\n

\u201cDo they serve soup there?\u201d<\/P>\n

\u201cI think so.\u201d<\/P>\n

\u201cThen forget it; I will never<\/I> set foot in any restaurant that serves soup! We either find a soup-free restaurant or we stay home.\u201d<\/P>\n

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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. As intriguing as the SOUP-FREE sign is, it still ranks as only the second<\/I>-most intriguing sign the Scripting Guy who writes this column has ever seen. But, then again, it\u2019s going to be hard to top the Certificate of Non-Significance<\/B><\/A>.<\/P>\n

And no, the Scripting Guy who writes this column doesn\u2019t actually have<\/I> a Certificate of Non-Significance. Some things are so obvious they don\u2019t need any kind of formal certification.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

If it turns out that you are violently opposed to the whole idea of soup, well, we have good news for you: today\u2019s solution is also 100% soup-free. The truth is, we didn\u2019t 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<\/I> way to copy an HTML table and then paste it into Excel:<\/P>

<Script Language=”VBScript”>\n    Sub RunScript\n        strCopy = MyTable.InnerHTML\n        document.parentwindow.clipboardData.SetData “text”, strCopy<\/p>\n
        Set objExcel = CreateObject(“Excel.Application”)\n        objExcel.Visible = True<\/p>\n
        Set objWorkbook = objExcel.Workbooks.Add()\n        Set objWorksheet = objWorkbook.Worksheets(1)\n        objWorksheet.Paste\n    End Sub\n<\/Script><\/p>\n
<body>\n    <span id=MyTable>\n        <table border=”1″ width=”100%” id=”table1″>\n    <tr>\n<td>Cell 1<\/td>\n<td>Cell 2<\/td>\n<td>Cell 3<\/td>\n    <\/tr>\n    <tr>\n<td>Cell 4<\/td>\n<td>Cell 5<\/td>\n<td>Cell 6<\/td>\n    <\/tr>\n    <tr>\n<td>Cell 7<\/td>\n<td>Cell 8<\/td>\n<td>Cell 9<\/td>\n    <\/tr>\n        <\/table>\n    <\/span>\n    <br>\n    <input id=runbutton type=”button” value=”Run Button” onClick=”RunScript”>\n<\/body>\n<\/PRE>\n
What we have here is a very simple little HTA, one that looks a lot like this:<\/P>\"HTA\" \n

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\u2019s email, we\u2019ve put this table inside a <SPAN> tag with the ID<\/B> MyTable; here\u2019s the code that creates the <SPAN> and initializes the table:<\/P>
<span id=MyTable>\n    <table border=”1″ width=”100%” id=”table1″>\n<\/PRE>\n
For those of you new to HTML tagging<\/B><\/A>, 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\u2019ve 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>.<\/P>\n
The long and short of it? We have a table in our HTA and<\/I> we have a mechanism for identifying and manipulating that table.<\/P>\n
We only have one other element in the HTA body: a button (labeled Run Button<\/B>) that, when clicked, runs a subroutine named RunScript:<\/P>
<input id=runbutton type=”button” value=”Run Button” onClick=”RunScript”>\n<\/PRE>\n
As you might expect, RunScript is the subroutine that\u2019s going to grab a copy of the table and somehow get that copy into an Excel spreadsheet.<\/P>\n
Good question: how does<\/I> this subroutine grab a copy of the table and get that copy into an Excel spreadsheet? Let\u2019s see if we can figure that out. <\/P>\n
To begin with, we use this line of code to connect to the MyTable <SPAN> and assign the value of the InnerHTML<\/B> property to a variable named strCopy:<\/P>
strCopy = MyTable.InnerHTML\n<\/PRE>\n
What\u2019s the InnerHTML property? That\u2019s 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\u2019s done we use this line of code to copy that information to the Windows Clipboard:<\/P>
document.parentwindow.clipboardData.SetData “text”, strCopy\n<\/PRE>\n
What we\u2019re doing here is calling the SetData<\/B> method, a method that just happens to belong to the clipboardData<\/B> object. In addition, we\u2019re passing SetData two parameters:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
“text”<\/B>, which represents the type of data being copied to the Clipboard.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
strCopy<\/B>, the actual data we want to copy to the Clipboard.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
As you probably guessed, after calling SetData our table will be copied to the Windows Clipboard; in turn, that means we\u2019re ready to paste this data into Excel. To that end, we first create an instance of the Excel.Application<\/B> object and then set the Visible<\/B> property to True:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\n<\/PRE>\n
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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
So then how do we paste in our table? Why, by calling the Paste<\/B> method, of course:<\/P>
objWorksheet.Paste\n<\/PRE>\n
Yes, just like a soup-free restaurant, this sounds too good to be true. But give it a try and see for yourself:<\/P>\"Microsoft \n

Truth be told, this is pretty darn easy; play around with this a little bit and you\u2019ll 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!<\/I> column:<\/P>\n
SCRIPT-FREE<\/P>\n
So does that mean that all the scripts in Hey, Scripting Guy!<\/I> are free of charge \u2026 or does that mean that Hey, Scripting Guy!<\/I> doesn\u2019t even try<\/I> 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.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,4,5,30],"class_list":["post-64783","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-vbscript","tag-web-pages-and-htas"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64783"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64783\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}