The CSV format is a spreadsheet format, quite popular among office workers, especially accountants, because of the convenience of preserving function structures, and previously executed commands, but the capacity is quite good. light.
In addition, CSV format can be used in Microsoft Excel applications, Google Sheests, Polaris Office, Libre Office, etc.). to read and edit similar Excel file.
However, the download of CSV files from the Internet to the computer and manipulate on Microsoft Excel sometimes occur some errors, as well as not fully utilize the tools, support on Excel applications.
Therefore, for your convenience when using CSV files, today I will share with you a little trick in convert CSV file format to XLS or XLSX in batch right on the Excel application with VBA code.
Read more:
#first. Convert CSV file to Excel by VBA (XLS format)
+ Step 1: First, open your Excel application. Here, you select the tab Developer => and click on Visual Basic. Or you can also press the key combination Alt + F11 to open the VBA code editor.
Note: If your Excel does not see the Developer tab, then see how to add Developer tab to Excel!
+ Step 2: Dialog box Microsoft Visual Basic for Applications open. Here you select the menu Insert => then click Module.
And copy the following code ....
Sub CSVtoXLS ()
‘UpdatebyExtendoffice20170814
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog (msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems (1)
Else
Exit Sub
End If
If Right (xSPath, 1) <> "" Then xSPath = xSPath + ""
xCSVFile = Dir (xSPath & "* .csv")
Do While xCSVFile <> ""
Application.StatusBar = "Converting:" & xCSVFile
Workbooks.Open Filename: = xSPath & xCSVFile
ActiveWorkbook.SaveAs Replace (xSPath & xCSVFile, ".csv", ".XLS", vbTextCompare), xlNormal
ActiveWorkbook.Close
Windows (xWsheet) .Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
=> .... And Paste into the dialog box Module1 (Code) => then press Run, or press the key F5 to execute.
+ Step 3: Next, the dialog box Select a Folder pop up, here you find the folder containing the file CSV => Click and press OK Let Excel conduct batch file conversion.
+ Step 4: And this is the result, the XLS files are in the Folder of the CSV file as shown.
#2. Convert CSV to XLSX file with VBA code
Beyond how to convert a CSV file to a format Excel 97-2003 as above. To convert a batch of CSV files to a format XLSX If you use Microsoft Excel 2007 or later, you will do the same.
The only difference is to replace the above code with the code below => and click Run or F5 on the keyboard at the dialog box Microsoft Visual Basic for Applications to run:
Sub CSVtoXLSX ()
‘UpdatebyExtendoffice20170814
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog (msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems (1)
Else
Exit Sub
End If
If Right (xSPath, 1) <> "" Then xSPath = xSPath + ""
xCSVFile = Dir (xSPath & "* .csv")
Do While xCSVFile <> ""
Application.StatusBar = "Converting:" & xCSVFile
Workbooks.Open Filename: = xSPath & xCSVFile
ActiveWorkbook.SaveAs Replace (xSPath & xCSVFile, ".csv", ".XLSX", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows (xWsheet) .Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
Then select the folder containing the CSV files => and then click OK to convert.
And we get the result Convert CSV file into XLSX format as shown below.
# 3. How to convert a CSV file to Excel online
In addition to using the code that I have just detailed above, you can also use how to convert CSV files to Excel with online tools quickly.
+ Step 1: You access this address: https://convertio.co/vn/csv-xls/ => Then click the button Chọn tập tin to upload the CSV file from your computer.
+ Step 2: Then you press the button Chuyển đổi => After conversion is complete, click download is done.
Generally, these online conversion sites I think anyone can use is simple. The process is as simple as downloading the file to be converted => then click convert => and download the converted file is done.
Every page is the same!
In addition to the above website, there are also some websites that also support the ability to convert CSV format to Excel online as well, including:
- https://www.zamzar.com/convert/csv-to-xls/
- https://onlineconvertfree.com/en/convert-format/csv-to-xls/
- You add more if any okay …………………………………………… ..
# 4. Epilogue
Okay, that's it then 😀 And so I have very detailed instructions for you two tips on applying VBA Code to perform Convert CSV files to Excel (XLS or XLSX format) batch quickly and effectively then.
At this point, my tutorial on how to convert a CSV file to XLS or XLSX in Excel in series, please also pause. Hope this tip will be helpful to everyone.
Good luck.
CTV: Luong Trung - Blogchiasekienthuc.com
Note: Was this article helpful to you? Do not forget to rate the article, like and share it with your friends and relatives!
0 Comments