One of the requirement on a project was a to split a excel file with multiple sheets to separate Tab delimited files. The challenge that we faced was that data was in Arabic Language and VB script was not getting it right.
Below is the Complete code how to split an excel into multiple UTF-8 Tab delimited files. The only importnat thing to note is that while saving file you need to provide argument 42 if you wish to save file in UTF-8 format.
oWorksheet1.SaveAs WScript.Arguments.Item(1), 42
The complete code is as follows and can be invoked from Batch script using
XlsToCsv.vbs Source_Excel.xls Test1.csv Test2.csv Test3.csv Test4.csv
- - - - Code Start Here -------
if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Dim oWorkbook
Dim oWorksheet1
Dim oWorksheet2
Dim oWorksheet3
Dim oWorksheet4
Dim RowCount
Dim ColCount
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Set oWorksheet1 = oWorkbook.Worksheets.Item(2)
Set oWorksheet2 = oWorkbook.Worksheets.Item(3)
Set oWorksheet3 = oWorkbook.Worksheets.Item(4)
Set oWorksheet4 = oWorkbook.Worksheets.Item(5)
oExcel.Application.DisplayAlerts = False
oWorksheet1.SaveAs WScript.Arguments.Item(1), 42
oExcel.Application.DisplayAlerts = False
oWorksheet2.SaveAs WScript.Arguments.Item(2), 42
oExcel.Application.DisplayAlerts = False
oWorksheet3.SaveAs WScript.Arguments.Item(3), 42
oExcel.Application.DisplayAlerts = False
oWorksheet4.SaveAs WScript.Arguments.Item(4), 42
oWorkbook.Close
oExcel.Quit
Set oWorksheet1 = Nothing
Set oWorksheet2 = Nothing
Set oWorksheet3 = Nothing
Set oWorksheet4 = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
--- Code End Here ----
Below is the Complete code how to split an excel into multiple UTF-8 Tab delimited files. The only importnat thing to note is that while saving file you need to provide argument 42 if you wish to save file in UTF-8 format.
oWorksheet1.SaveAs WScript.Arguments.Item(1), 42
The complete code is as follows and can be invoked from Batch script using
XlsToCsv.vbs Source_Excel.xls Test1.csv Test2.csv Test3.csv Test4.csv
- - - - Code Start Here -------
if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Dim oWorkbook
Dim oWorksheet1
Dim oWorksheet2
Dim oWorksheet3
Dim oWorksheet4
Dim RowCount
Dim ColCount
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Set oWorksheet1 = oWorkbook.Worksheets.Item(2)
Set oWorksheet2 = oWorkbook.Worksheets.Item(3)
Set oWorksheet3 = oWorkbook.Worksheets.Item(4)
Set oWorksheet4 = oWorkbook.Worksheets.Item(5)
oExcel.Application.DisplayAlerts = False
oWorksheet1.SaveAs WScript.Arguments.Item(1), 42
oExcel.Application.DisplayAlerts = False
oWorksheet2.SaveAs WScript.Arguments.Item(2), 42
oExcel.Application.DisplayAlerts = False
oWorksheet3.SaveAs WScript.Arguments.Item(3), 42
oExcel.Application.DisplayAlerts = False
oWorksheet4.SaveAs WScript.Arguments.Item(4), 42
oWorkbook.Close
oExcel.Quit
Set oWorksheet1 = Nothing
Set oWorksheet2 = Nothing
Set oWorksheet3 = Nothing
Set oWorksheet4 = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
--- Code End Here ----
