{"id":67813,"date":"2006-03-07T11:29:00","date_gmt":"2006-03-07T11:29:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/03\/07\/how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html\/"},"modified":"2006-03-07T11:29:00","modified_gmt":"2006-03-07T11:29:00","slug":"how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html\/","title":{"rendered":"How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I save an Excel spreadsheet, and then save a copy of that spreadsheet as HTML?

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

Hey, TH. You know, it\u2019s interesting that you used the word \u201csave\u201d in your question. After all, the Scripting Guy who writes this column (who\u2019s much lazier – um, much busier<\/I> than usual) just glanced at the clock and realized, \u201cUh-oh: I haven\u2019t written tomorrow\u2019s column yet.\u201d There were only two things that could save him: 1) the end of the world (though no doubt even then<\/I> his editor would want to know where the column was) or, 2) a question that he could answer without having to do any research. TH, you saved the Scripting Guys!<\/P>\n

Well, at least this<\/I> Scripting Guy.<\/P>\n

In return for your heroic action we\u2019d like to give you something near and dear to our hearts: a script that can save an Excel spreadsheet, and then save a copy of that spreadsheet as HTML. That\u2019s OK; don\u2019t bother to hide your tears. This is a pretty emotional moment for us, too.<\/P>\n

Here\u2019s your script:<\/P>

Const xlHTML = 44 <\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objExcel.DisplayAlerts = False<\/p>\n
objExcel.Cells(1, 1).Value = “A”\nobjExcel.Cells(1, 2).Value = “B”\nobjExcel.Cells(1, 3).Value = “C”\nobjExcel.Cells(1, 4).Value = “D”<\/p>\n
objWorkbook.SaveAs “C:\\Scripts\\Test.xls”\nobjWorkbook.SaveAs “C:\\Scripts\\Test.htm”, xlHTML\n<\/PRE>\n
You can see why – facing a deadline and almost certain doom – we decided to answer this<\/I> question: there\u2019s hardly anything at all to the script. We start off by defining a constant named xlHTML and setting the value to 44; we\u2019ll use this constant to tell Excel that we want to save a copy of the spreadsheet as HTML. We then have four lines of code that create a visible instance of the Excel.Application<\/B> object and then create a new workbook and a new worksheet for us to play around with.<\/P>\n
And then, just for the heck of it, we threw in this line of code:<\/P>
objExcel.DisplayAlerts = False\n<\/PRE>\n
Why did we do this? Well, first we\u2019re creating a brand-new worksheet with this script, and then we\u2019re going to save it as C:\\Scripts\\Test.xls (and then as C:\\Scripts\\Test.htm). That\u2019s fine, unless<\/I> a file named Test.xls or Test.htm already exists. In that case Excel won\u2019t automatically overwrite the existing file; instead, it will pop up a dialog box asking if you\u2019re sure that you want to overwrite that file. The dialog box will sit there on screen until you click Yes, No, or Cancel; needless to say, that means your script will also sit there – doing nothing – until you click Yes, No, or Cancel.<\/P>\n
Unless, that is, you set DisplayAlerts<\/B> to False. When DisplayAlerts is set to False Excel will not display any message boxes; instead, it automatically uses the default option of any message boxes it would otherwise show. For example, the default option for overwriting an existing file is Yes; therefore, instead of displaying a message box and waiting for you to answer, the script simple goes with the default value (yes) and overwrites the file. Make sense?<\/P>\n
And, no, we didn\u2019t have<\/I> to use this option here. However, we get a lot of questions about this (\u201cHow can I stop Excel from asking me if I want to overwrite a file?\u201d), and this seemed like a sneaky way to answer two questions for the price of one. Although, now that we think about it, it would have been smarter to save that<\/I> question for tomorrow\u2019s column, putting us ahead of the game for once. Dang.<\/P>\n
After configuring the DisplayAlerts property we have some code that simply writes the values A, B, C, and D into four cells of the spreadsheet. That brings us to the last two lines of the script:<\/P>
objWorkbook.SaveAs “C:\\Scripts\\Test.xls”\nobjWorkbook.SaveAs “C:\\Scripts\\Test.htm”, xlHTML\n<\/PRE>\n
In the first line we\u2019re saving our spreadsheet as a spreadsheet; that\u2019s done by calling the SaveAs<\/B> method and including just one parameter, the full path to the new file. In the second line we\u2019re saving a copy of the spreadsheet as an HTML file. Note that we also use the SaveAs<\/B> method and that we again specify the full path to the new file (this time with a .htm file extension). However, we also add a second parameter, the constant xlHTML; as we noted earlier, this tells Excel the file format for the new file. Could we save a file in a format other than HTML? Of course; hey, variety is the spice of life, right? Here are a just few of the constants and their corresponding values that we can use with the SaveAs method:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Constant<\/B><\/P><\/TD>\n
\n
Value<\/B><\/P><\/TD><\/TR>\n
\n
xlCSV<\/P><\/TD>\n
\n
6<\/P><\/TD><\/TR>\n
\n
xlCSVMac<\/P><\/TD>\n
\n
22<\/P><\/TD><\/TR>\n
\n
xlCSVMSDOS<\/P><\/TD>\n
\n
24<\/P><\/TD><\/TR>\n
\n
xlCSVWindows<\/P><\/TD>\n
\n
23<\/P><\/TD><\/TR>\n
\n
xlDBF4<\/P><\/TD>\n
\n
11<\/P><\/TD><\/TR>\n
\n
xlExcel9795<\/P><\/TD>\n
\n
43<\/P><\/TD><\/TR>\n
\n
xlHtml<\/P><\/TD>\n
\n
44<\/P><\/TD><\/TR>\n
\n
xlSYLK<\/P><\/TD>\n
\n
2<\/P><\/TD><\/TR>\n
\n
xlTemplate<\/P><\/TD>\n
\n
17<\/P><\/TD><\/TR>\n
\n
xlTextWindows<\/P><\/TD>\n
\n
20<\/P><\/TD><\/TR>\n
\n
xlXMLData<\/P><\/TD>\n
\n
47<\/P><\/TD><\/TR>\n
\n
xlXMLSpreadsheet<\/P><\/TD>\n
\n
46<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
For a complete list, take a peek at the Excel VBA Language Reference<\/B><\/A> on MSDN. (Look for the xlFileFormat<\/B> enumeration.)<\/P>\n
So have the Scripting Guys learned their lesson from this close escape? Will they now do a better job of getting their columns in before<\/I> the deadline? You bet they will; in fact, you can count<\/I> on it.<\/P>\n
Just don\u2019t count on it.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I save an Excel spreadsheet, and then save a copy of that spreadsheet as HTML?— TH Hey, TH. You know, it\u2019s interesting that you used the word \u201csave\u201d in your question. After all, the Scripting Guy who writes this column (who\u2019s much lazier – um, much busier than usual) just […]<\/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,5],"class_list":["post-67813","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I save an Excel spreadsheet, and then save a copy of that spreadsheet as HTML?— TH Hey, TH. You know, it\u2019s interesting that you used the word \u201csave\u201d in your question. After all, the Scripting Guy who writes this column (who\u2019s much lazier – um, much busier than usual) just […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67813","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=67813"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67813\/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=67813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}