Many different csv documents to be convertered to one excel document

I have many documents in CSV format(about 100 different .csv files). I want to merge all of them into one .xls document. I want each CSV document to represent a worksheet in my new xls document. So my final result should be one xls workbook w开发者_开发知识库ith 100 different worksheets which were originally .csv documents.

I'd use a scripting language such as vbs. It plays well with Excel and other MS Applications, and you can run it from either command line or double clicking on the file.

You need to add values for those two arguments or

If you do command line you can pass the srccsvfile and tgtxlsfile parameters like:

cscript nameoffile.vbs test.csv test.xls

Here is some sample code. Save as nameoffile.vbs:

srccsvfile = Wscript.Arguments(0)  
tgtxlsfile = Wscript.Arguments(1)  

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    objExcel.Columns(intColumns).AutoFit()
'Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = TRUE

'Freeze header row
With objExcel.ActiveWindow
 .SplitColumn = 0
 .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True

'Add Data Filters to Heading Row
objExcel.Rows(1).AutoFilter

'set header row gray
objExcel.Rows(1).Interior.ColorIndex = 15
'-0.249977111117893


'Save Spreadsheet, 51 = Excel 2007-2010 
objWorksheet1.SaveAs tgtxlsfile, 51

'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing