With this VBA code you will be able to import a CSV file to a worksheet and you can export a selected range of cells to a CSV file. The code has the advantage of working with various delimited characters. So, apart from comma (“,”) you can use semicolon (“;”), hyphen (“-“), pipe character (“|”) etc.
Option Explicit
Sub ImportCSV()
'------------------------------------------
'Imports a CSV file to an excel worksheet.
'By Christos Samaras
'https://myengineeringworld.net/////
'-----------------------------------------
Dim shtImport As Worksheet
Dim strDelChar As String
Dim strFileName As String
Dim lRow As Long
Dim lCol As Long
Dim strText As String
Dim strChar As String * 1
Dim vntData As Variant
Dim lCharCount As Long
'Change the name "Import" according to your sheet name.
Set shtImport = Sheets("Import")
'Change the delimited character "," according to your own needs.
'NOTE: Due to the code structure only one character is accepted
' for delimited character (example "%", "-", "|" etc.).
strDelChar = ","
'Show the file dialog and select a CSV file.
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Select a CSV file!"
.Filters.Clear
.Filters.Add "Comma Separated Values", "*.csv"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "You did't select a text file!", vbExclamation, "Canceled"
Exit Sub
Else
strFileName = .SelectedItems(1)
End If
End With
Application.ScreenUpdating = False
'Check if the selected file is CSV file.
If UCase(Right(strFileName, 3)) <> "CSV" Then
MsgBox "The file you select is not a CSV file!", vbCritical, "Error!"
Exit Sub
End If
'Open the CSV file.
On Error Resume Next
Open strFileName For Input As #1
'Check if the file was opened.
If Err <> 0 Then
MsgBox "File not found: " & strFileName, vbCritical, "Error"
Exit Sub
End If
On Error GoTo 0
'Initializing the variables.
lRow = 0
lCol = 0
strText = ""
'Activate the A1 cell on Import sheet.
shtImport.Activate
Range("A1").Activate
'Loop through all lines of CSV file and import the data values to the active sheet.
Do Until EOF(1)
'Pass the line data to a variable.
Line Input #1, vntData
'Loop through all characters of the variable.
For lCharCount = 1 To Len(vntData)
'Examine each character separately.
strChar = Mid(vntData, lCharCount, 1)
'If reach the delimited character write the value to a cell.
If strChar = strDelChar Then
ActiveCell.Offset(lRow, lCol) = strText
lCol = lCol + 1
strText = ""
'If reach the end of the line write the (last) value to a cell and
'then reset the strText variable.
ElseIf lCharCount = Len(vntData) Then
If strChar <> Chr(34) Then strText = strText & strChar
ActiveCell.Offset(lRow, lCol) = strText
strText = ""
'In any other case concatenate the character with the strText variable.
ElseIf strChar <> Chr(34) Then
strText = strText & strChar
End If
Next lCharCount
'Reset the variables for the next line of data.
lCol = 0
lRow = lRow + 1
Loop
'Close the CSV file.
Close #1
Application.ScreenUpdating = True
'Inform the user about the process.
MsgBox "The file " & strFileName & " was successfully imported on sheet " & _
shtImport.Name & "!", vbInformation, "Done"
End Sub
Export: Select a range of cells and the following code will create a CSV file with the selected values. Note, that the exported CSV file will be created in the same folder with the workbook that contains the code.
Option Explicit
Sub ExportToCSV()
'----------------------------------------
'Exports a selected range to a CSV file.
'By Christos Samaras
'https://myengineeringworld.net/////
'----------------------------------------
Dim strFileName As String
Dim strDelChar As String
Dim rngExportData As Range
Dim lNumRows As Long
Dim lNumCols As Long
Dim lRow As Long
Dim lCol As Long
Dim vntTemp As Variant
Dim vntData As Variant
'Change the name "Exported Results.csv" according to your own needs.
'You can also directly set the full path of the file, for example:
'strFileName = "D:Results.csv"
strFileName = ThisWorkbook.Path & "" & "Exported Results.csv"
'Change the delimited character "," according to your own needs.
'NOTE: Although you can use more than one character as delimited
' character(s) (example "-|-", I suggested you to use only one
' (example "%", "-", "|" etc.) in order to be able to use the ImportCSV macro.
strDelChar = ","
'Assign the selected range to a range variable.
Set rngExportData = Application.Intersect(Selection, ActiveSheet.UsedRange)
'Check if the selected range is empty.
If rngExportData Is Nothing Then
MsgBox "The export range is nothing!", vbCritical, "Error"
Exit Sub
End If
Application.ScreenUpdating = False
'Find the number of rows and columns.
lNumRows = rngExportData.Rows.Count
lNumCols = rngExportData.Columns.Count
'Open the CSV file.
On Error Resume Next
Open strFileName For Output As #1
'Check if the file was opened.
If Err <> 0 Then
MsgBox "File not found: " & strFileName, vbCritical, "Error"
Exit Sub
End If
On Error GoTo 0
'Loop through all the rows and columns of selected range.
For lRow = 1 To lNumRows
'Use a temporary variable to created the delimited format.
vntTemp = Empty
If lNumCols > 1 Then
'There must be no delimited before the first value of each column.
vntData = rngExportData.Cells(lRow, 1).Value
vntTemp = vntTemp & vntData
'After the second column the delimited character is used.
For lCol = 2 To lNumCols
vntData = rngExportData.Cells(lRow, lCol).Value
vntTemp = vntTemp & strDelChar & vntData
Next lCol
Else
'If there is only one column, no delimited is used.
vntData = rngExportData.Cells(lRow, 1).Value
vntTemp = vntTemp & vntData
End If
'Write the data from each row to the file.
Print #1, vntTemp
Next lRow
'Close the CSV file.
Close #1
Application.ScreenUpdating = True
'Inform the user about the process.
MsgBox "The file " & strFileName & " was successfully created!", vbInformation, "Done"
End Sub
The short video below demonstrates the two macros in use.
Great code!! & very well documented
Thanks a lot Christos for share your knolwdge…
JMD
Thank you very much for your kind comment, Javier.
If the above code helped you, then I am happy… 😉
I just sent you an email… 😉
Kind Regards,
Christos
Hi Christos
I have sent a mail to you, I'm trying to do some similar job.
I will really appreciate your help.
Best Regards!
R.