Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Export Range of Cells in Excel to CSV File
CSV (Comma−Separated Values) is a plain text file format used to store tabular data. It is a widely supported and commonly used format for exchanging data between different applications, particularly in spreadsheet software like Microsoft Excel, Google Sheets, and others. When working with CSV files, it's essential to be mindful of the delimiter used, handle special characters appropriately, and ensure data integrity and consistency.
Steps to Export a Range of Cells in Excel to CSV
To export range of cells in Excel data to CSV (Comma−Separated Values) format, you can follow these steps:
Step 1
Open your Excel workbook where the data contains. Press Alt+F11 to open the VBA Editor. Insert a new module by clicking Insert −> Module.
Copy and paste the following code in to the newly added Module.
Example
Sub ExportRangeToCSV()
Dim wb As Workbook
Dim ws As Worksheet
Dim savePath As String
Dim saveFileName As String
Dim rng As Range
' Set the workbook and worksheet variables
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Prompt user to select the range
On Error Resume Next
Set rng = Application.InputBox("Select the range to export:", Type:=8)
On Error GoTo 0
' Check if user canceled the selection
If rng Is Nothing Then
Exit Sub
End If
' Prompt user for save location and filename
savePath = Application.GetSaveAsFilename(FileFilter:="CSV Files (*.csv), *.csv")
' Check if user canceled the save dialog
If savePath = "False" Then
Exit Sub
End If
' Get the file name from the full path
saveFileName = Dir(savePath)
' Export the range to CSV
With CreateObject("Scripting.FileSystemObject")
Dim file As Object
Set file = .CreateTextFile(savePath, True)
Dim row As Range
For Each row In rng.Rows
Dim cell As Range
For Each cell In row.Cells
file.Write cell.Value & ","
Next cell
file.WriteLine
Next row
file.Close
End With
MsgBox "Selected range exported to CSV successfully."
End Sub
Step 2
Modify the code to set the correct worksheet name and range you want to export. Save and close the VBA Editor.
Press Alt+F8 to open the Macro dialog box. Select the ExportRangeToCSV macro and click Run.
Step 3
After clicking on the Run the code will prompt for selecting the Range of cells that you want to export.
Step 4
After selecting the Range and clicking on OK then the code will prompt for Save As dialog, there you need to enter the filename and the desired location where you want to save the CSV file with default Save as type as CSV File.
Step 5
After clicking on the Save the file will be save in the desired location.
The file and the output would look like the one shown below:
Conclusion
In summary, exporting a selected range of data from an Excel worksheet to a CSV file using VBA is entirely feasible. By utilizing VBA code, users can dynamically select the desired range during the macro execution, prompting an interactive selection process. The code provided successfully exports the chosen range to a CSV file, eliminating the repetition of header rows. This streamlined solution ensures that the exported CSV file contains the relevant data without redundancy.
With this VBA functionality, users can efficiently export specific data ranges from their Excel workbooks to CSV files, simplifying data management and facilitating further analysis or integration with other applications.
979 Views
