Fin
2014-03-07 21:50:15 UTC
I have the following sample script that I use to convert CSV to Excel 2010 files, which works just fine. I am looking for help on how to amend this so I can create multiple tabs from multiple CSV files, all within a single Excel 2010 file.
So it should open a new Excel file, load the first CSV into say Sheet1, then create and switch to say Sheet2, and load another different CSV file saving the data to said Sheet2.
Can some kind soul please give me some idea on how this may be achieved with some sample code ??
Many thanks, Fin.
--
' To execute CSV_TO_EXCEL.VBS FileType InFile OutFile ActDate
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Const xlValues = -4163
Const xlUp = -4163
Const xlDown = -4121
Const xlPart = 1
' input parameters
FileType = Wscript.Arguments.Item(0)
InFile = Wscript.Arguments.Item(1)
OutFile = Wscript.Arguments.Item(2)
ActDate = Wscript.Arguments.Item(3)
Dim objXLApp, objXLWb, objXLWs
Dim currentValue, compareValue, ErrorRow, LastCol, DateRow, CurrCol
Dim CurrDateValue, CompDateValue, DateCompare
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
objXLApp.DisplayAlerts = False
Set objXLWb = objXLApp.Workbooks.Open(InFile)
' Working with Sheet1
Set objXLWs = objXLWb.Sheets(1)
' Freeze the Pane for the Column Header Row
With objXLApp.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
objXLApp.ActiveWindow.FreezePanes = True
' Autofit ALL columns
Set objRange = objXLWs.UsedRange
objRange.EntireColumn.Autofit()
' Set range and count Row & Columns
Set objRange = objXLWs.UsedRange
iRows = objRange.Rows.Count
iColumns = objRange.Columns.Count
' Check the row count to quit if file is empty
If iRows < 2 then 'there will be a header row
objXLWb.Close (True)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing
Wscript.Quit
End If
' Move the cursor to Cell A2 for exit
objXLApp.Range("A2").Select
' Save as Excel 2010 File (xlsx) to retain format
objXLWb.SaveAs OutFile, 51
objXLWb.Close (True)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing
So it should open a new Excel file, load the first CSV into say Sheet1, then create and switch to say Sheet2, and load another different CSV file saving the data to said Sheet2.
Can some kind soul please give me some idea on how this may be achieved with some sample code ??
Many thanks, Fin.
--
' To execute CSV_TO_EXCEL.VBS FileType InFile OutFile ActDate
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Const xlValues = -4163
Const xlUp = -4163
Const xlDown = -4121
Const xlPart = 1
' input parameters
FileType = Wscript.Arguments.Item(0)
InFile = Wscript.Arguments.Item(1)
OutFile = Wscript.Arguments.Item(2)
ActDate = Wscript.Arguments.Item(3)
Dim objXLApp, objXLWb, objXLWs
Dim currentValue, compareValue, ErrorRow, LastCol, DateRow, CurrCol
Dim CurrDateValue, CompDateValue, DateCompare
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
objXLApp.DisplayAlerts = False
Set objXLWb = objXLApp.Workbooks.Open(InFile)
' Working with Sheet1
Set objXLWs = objXLWb.Sheets(1)
' Freeze the Pane for the Column Header Row
With objXLApp.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
objXLApp.ActiveWindow.FreezePanes = True
' Autofit ALL columns
Set objRange = objXLWs.UsedRange
objRange.EntireColumn.Autofit()
' Set range and count Row & Columns
Set objRange = objXLWs.UsedRange
iRows = objRange.Rows.Count
iColumns = objRange.Columns.Count
' Check the row count to quit if file is empty
If iRows < 2 then 'there will be a header row
objXLWb.Close (True)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing
Wscript.Quit
End If
' Move the cursor to Cell A2 for exit
objXLApp.Range("A2").Select
' Save as Excel 2010 File (xlsx) to retain format
objXLWb.SaveAs OutFile, 51
objXLWb.Close (True)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing