Join Curt Frye for an in-depth discussion in this video Saving a workbook as a CSV file, part of Excel VBA: Managing Files and Data.
- Microsoft Excel is one of the most popular software programs in the world, but that said not everyone has it, or not everyone cares to use it for every task. If that's the case and you need to transfer your Excel data to another program that can't read Excel you can almost always go through coma-separated value files. What CSV files do is contain data with individual values separated by comas, so they're extremely versatile, extremely easy to use, and also they don't take up much space because they're all text.
In this movie I will use VBA to save an Excel Worksheet, the active Worksheet, as a CSV file. My sample file is "SaveAsCSV", and you can find it in the chapter two folder of your exercise files collection. So I have a Workbook that has only a single Worksheet, and I'll go ahead and press "Alt+F11" to switch over to the Visual Basic Editor. And here I have a code module with a subroutine called "SaveAsCSV", and there's a little bit of code already in there, but I don't have to deal with it yet, I'll explain it later on in the movie.
If you want to save the active Workbook as a CSV file you use this command, that is, "ActiveWorkbook.SaveAs". So this is the same command that you would use to save a file under a new name, then a space, and "Filename", so we have to give it a filename, ":=". The filename is a text string so I need to put it in double quotes, and I'll call it, "NewCSV.csv", and then close the double quote, then a coma, and now I need to indicate the file format.
So that's the parameter "FileFormat :=", and then I need to type in a variable that indicates that format, and for a CSV file that's actually very intuitive, it's "xl" lower case, and then capital "CSV". So what I have done, press "Enter", is to indicate that I want to save the active Workbook under the filename "NewCSV.csv", and I'll use the file format of "xlCSV" for coma-separated value.
And because the filename only contains the name of the file and not the directory path, the file will be saved in the same folder as the file that I'm working with, which is "SaveAsCSV.xlsm". But let's say that I want to specify the path where I want the file to go, to that I can display the folder that I want the file to end up in in Windows Explorer, and I'm in the chapter two folder of the exercise files collection. I will click the folder at the left edge of the Navigation Bar to display the path to this folder, it's highlighted in blue so I'll press "Ctrl+C" to copy it, click the Title Bar for the Visual Basic Editor to switch back, and then I'll click just inside the string with the new filename and press "Ctrl+V" to paste.
Now you can see here at the edge, at the end of what I pasted that there is no backslash between "Chapter02" and the filename, so I'll go ahead and add that. The rest of my command runs off the screen, so I will use the underscore character to indicate that I want the command to continue on the new line, and press "Enter". So you can see by clicking down here that the underscore allows the command to go on to a second line. That's very handy if you're writing extremely long lines of code in VBA.
So now I've specified the file path or folder where I want the file to be saved. I have indicated the name and I've indicated the format, so I will go ahead and press "F5" to run it, and my file was saved. And you can see on the Title Bar that the name of the file has changed, it is now "NewCSV.csv". If I were to run the code again by pressing "F5" I will get an Alert Box indicating that a file the same name already exists, and asking if I want to replace it. In this case I will click Cancel.
And Visual Basic throws an error so I click End. If I want to avoid that Alert Box I can turn AlertDisplay off, and to do that I use the commands at the top and bottom of my subroutine. So I have "Application.DisplayAlerts = False" at the top, and what that command does is to suppress Alert Boxes like the one indicating that there is already a file with the same name as the file I was trying to save. So that turns those alerts off and it just bypasses them.
And at the bottom I turn those alerts back on by setting the same property to "True". So now if I press "F5" the code simply runs. Saving your Excel work with data as a CSV file makes the files even more compatible than they already are. So if you need to communicate data with someone who absolutely can't read an Excel file, CSV is probably the way to go.
- Creating filters
- Chaining and combining filter criteria
- Determining whether workbooks and worksheets exist, with VBA
- Opening, closing, and saving workbooks
- Using VBA to calculate data via Excel's built-in functions
- Creating charts with sparklines
- Adding fields to UserForms