Visual Basic for Applications (VBA) is a great tool for the automatization of our work. It can also be used to manipulate any Office application (mostly Excel) on any level (sheet, workbook, or application as a whole).
In the example below, we will show how can we manipulate the worksheet from the VBA by saving the worksheet in a specific file type – a CSV file.
Save Excel Worksheet as CSV with VBA
For our example, we will use the table with NBA players and their respective teams:
Purposefully, we have started with row number 3. To open the VBA, we will click ALT + F11, then right-click in the left module and choose Insert >> Module:
In the window that appears on the right side, we will insert the following code:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub SaveSheetAsCSV() Dim ws As Worksheet Dim csvFile As String Set ws = ActiveSheet ws.Copy csvFile = CurDir & "\" & ws.Name & ".csv" Application.ActiveWorkbook.SaveAs Filename:=csvFile, _ FileFormat:=xlCSV, CreateBackup:=False Application.ActiveWorkbook.Saved = TRUE Application.ActiveWorkbook.Close End Sub |
This is what a code looks like in the VBA:
This code first declares two variables: ws as Worksheet and csvFile as String.
Then we set ws to be equal to the active sheet (the one we are currently on). Of course, we can set this sheet to be anything we like.
The next step:
|
1 2 3 |
csvFile = CurDir & "\" & ws.Name & ".csv" Application.ActiveWorkbook.SaveAs Filename:=csvFile, _ FileFormat:=xlCSV, CreateBackup:=False |
Saves our file (our worksheet) in the current directory (it is set to be in MyDocuments for all users), and then it saves it under the name that will be equal to the name of our sheet („NBA players“ in this case), and it saves is in CSV file format.
The code finally saves the workbook (copied one), and then it closes it. The important step here is to copy our Worksheet into the new workbook and to have the original file opened as well.
When we execute the code by pressing F5 on our keyboard, we will only see something happening in the background.
Now we need to go to our CurDir folder– it is set to be C:\Users\[username]\Documents folder.
In there, we will be able to see the CSV file created:
When we open the file, you will notice that the list of NBA players in the CSV file starts from the first row:
