How Can I Save a Single Excel Worksheet to a CSV File?

ScriptingGuy1

Hey, Scripting Guy! I’d like to be able to open an Excel spreadsheet, get all the information off one of the worksheets, and then save that worksheet data to a comma-separated values file. How can I do that?

— SS

Hey, SS. You know, you just have to love Excel. Yes, we know, to be “cool” these days you’re supposed to say that you don’t use Excel, that you use one of those Office wannabes instead. But we’d like to see one of those Office wannabes carry out a task like this, and in just a few lines of code to boot.

Now, to be honest, when we first read the question we weren’t sure how easy this was going to be. Turns out it was this easy:

Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Testsheet.xls")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = TRUE

Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV

objExcel.Quit

That’s it; that’s the whole script right there. We begin by creating a constant named xlCSV and setting the value to 6; we’ll use this constant later on to specify the file format for our new file. We then create an instance of the Excel.Application object and use the Workbooks.Open method to open the spreadsheet C:\Scripts\Testsheet.xls. (We also set the Visible property to TRUE, but that’s just so you can see what’s going on. If you’d prefer to have Excel run in an invisible window, then just leave this line out.)

Oh, and we have one other line of code that might be of interest:

objExcel.DisplayAlerts = FALSE

This line of code tells Excel not to display any message boxes, but to instead go with the default value any time it has a question. Why do we bother with this? Well, we’re going to save worksheet information to a CSV (comma-separated values) file. If that file already exists Excel will pop up a message box asking if we want to overwrite the existing file. For this script we’re assuming that we always want to overwrite the existing file. By setting the DisplayAlerts property to FALSE we suppress the display of that message box and tell Excel to go ahead and carry out the default operation. In this case, the default operation just happens to be, “Go ahead and overwrite any existing file.”

After we have Excel up and running it takes just two lines of code to save worksheet data to a CSV file. First, we bind to the desired worksheet (in this case, the worksheet named “Sheet1”):

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

After that we simply call the SaveAs method, passing two parameters: the path to CSV file (C:\Scripts\Test.csv) and the format for the new file. Because we want this saved as a CSV file, we use the xlCSV constant we defined way back when. Thus:

objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV

All we have to do now is call the Quit method to terminate our Excel instance and we’re done. Now try and tell us that one of those Excel wannabes is as good as the real thing!

Shameless Self-Promotion: If you’re interested in scripting Microsoft Office applications, be sure and check out the Learn about Scripting for Microsoft Office.  

ScriptingGuy1

Follow