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’s interesting that you used the word “save” in your question. After all, the Scripting Guy who writes this column (who’s much lazier – um, much busier than usual) just glanced at the clock and realized, “Uh-oh: I haven’t written tomorrow’s column yet.” There were only two things that could save him: 1) the end of the world (though no doubt even then 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!

Well, at least this Scripting Guy.

In return for your heroic action we’d 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’s OK; don’t bother to hide your tears. This is a pretty emotional moment for us, too.

Here’s your script:

Const xlHTML = 44 

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.DisplayAlerts = False
objExcel.Cells(1, 1).Value = “A”
objExcel.Cells(1, 2).Value = “B”
objExcel.Cells(1, 3).Value = “C”
objExcel.Cells(1, 4).Value = “D”
objWorkbook.SaveAs “C:\Scripts\Test.xls”
objWorkbook.SaveAs “C:\Scripts\Test.htm”, xlHTML

You can see why – facing a deadline and almost certain doom – we decided to answer this question: there’s hardly anything at all to the script. We start off by defining a constant named xlHTML and setting the value to 44; we’ll 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 object and then create a new workbook and a new worksheet for us to play around with.

And then, just for the heck of it, we threw in this line of code:

objExcel.DisplayAlerts = False

Why did we do this? Well, first we’re creating a brand-new worksheet with this script, and then we’re going to save it as C:\Scripts\Test.xls (and then as C:\Scripts\Test.htm). That’s fine, unless a file named Test.xls or Test.htm already exists. In that case Excel won’t automatically overwrite the existing file; instead, it will pop up a dialog box asking if you’re 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.

Unless, that is, you set DisplayAlerts 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?

And, no, we didn’t have to use this option here. However, we get a lot of questions about this (“How can I stop Excel from asking me if I want to overwrite a file?”), 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 question for tomorrow’s column, putting us ahead of the game for once. Dang.

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:

objWorkbook.SaveAs “C:\Scripts\Test.xls”
objWorkbook.SaveAs “C:\Scripts\Test.htm”, xlHTML

In the first line we’re saving our spreadsheet as a spreadsheet; that’s done by calling the SaveAs method and including just one parameter, the full path to the new file. In the second line we’re saving a copy of the spreadsheet as an HTML file. Note that we also use the SaveAs 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:

For a complete list, take a peek at the Excel VBA Language Reference on MSDN. (Look for the xlFileFormat enumeration.)

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 the deadline? You bet they will; in fact, you can count on it.

Just don’t count on it.