Save Excel Worksheet as CSV with VBA

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:

Graphical user interface, table

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

In the window that appears on the right side, we will insert the following code:

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:

Graphical user interface, text, application, email

Description automatically generated

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:

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:

Application

Description automatically generated with medium confidence

When we open the file, you will notice that the list of NBA players in the CSV file starts from the first row:

Table

Description automatically generated

Posted in vba