-
-
Save JamoCA/62666b5ef89ec54ef3c2 to your computer and use it in GitHub Desktop.
'====================================== | |
'Visual Basic Script to convert CSV file to XLSX. (Excel is required to be installed.) | |
'http://stackoverflow.com/a/31606893/693068 | |
'Further customization ideas and information regarding permissions at: | |
'http://jeffkinzer.blogspot.com/2010/06/vbscript-to-convert-csv-to-xlsx.html | |
' | |
'FORMAT: CSCRIPT CSV_To_XLSX.vbs "c:\base\data.csv" | |
'(Note: CSCRIPT is required to run VBS file using the command line.) | |
'====================================== | |
Dim file, WB | |
With CreateObject("Excel.Application") | |
On Error Resume Next | |
For Each file In WScript.Arguments | |
Set WB = .Workbooks.Open(file) | |
WB.SaveAs Replace(WB.FullName, ".csv", ".xlsx"), 51 | |
WB.Close False | |
Next | |
.Quit | |
End With | |
WScript.Echo "Done!" |
To change the sheet name:
WB.Sheets(1).Name = "Devharsh"
To run this script for all csv in directory:
#!/usr/bin/perl
use strict;
use autodie;
my $directory = $ARGV[0];
opendir(DIR, $directory) or die $!;
while (my $file = readdir(DIR))
{
if(-f "$directory/$file")
{
if ($file =~ /\.csv$/)
{
system("CSV_To_XLSX.vbs \"$directory/$file\"");
}
}
}
closedir(DIR);
Thanks for sharing this. This converted my files to .xlsx. Is there a way to incorporate VBS code inside VBA? I can do this task in VBA but my problem is that it's taking so long before the task to be completed. Mainly because of opening large files in Excel. When I use VBS for the same task, it's way faster than VBA. So, I'm thinking if it's possible to incorporate these two. I would also need to make some customization because the file name and file path is inside the workbook. Thanks.
Note: I know that VBS and VBA are somehow related but I only know the syntax of VBA.
Thank you for sharing. In case you dont have Excel installed you can use an online converter
Hi Jamo,
I am new to VB and wanted to know how do I run this , or auto run this.
My problem is that I have to convert all csv's in a folder to .xlsx
& then merge all of these into One single workbook.
Would you know a workaround ? I am sure your above script will help but I am not sure how do I run this.