Convert CSV to Xls 1

[karelsmits]

Dear All,

I am trying to convert csv files to xls files. The csv files should be converted in batch mode, meaning that I have loads of csv files in a folder that keep being updated from time to time, and need to be converted to xls. In other words it is a complete folder of csv-files that need to be converted to xls files.
I found a script online which does exactly that. It opens the file in the background with Excel and saves it as an xls file. It is as if I open the file in Excel and save it as xls. The difference is that it is done automatically with that script.
Problem is: the script, which i attached, does only 1 specified file at a time. I need to be able to have a whole folder converted.

Please let me know what you guys think of it, you do not have to use for a basis the below script if you have a better way of doing it.
Thank you very much.

SCRIPT:
==============================================
==============================================


Option Explicit
Const vbNormal = 1

DIM objXL, objWb, objR ' Excel object variables
DIM Title, Text, tmp, i, j, file, name, savename

file = "IBRENTAL.csv"
name = "IBRENTAL"

savename = "c:\rjm\IBRENTAL.xls"

Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function



' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window


' Load the Excel file from the script's folder
Set objWb = objXl.WorkBooks.Open(GetPath+file)

' Get the loaded worksheet object
Set objWb = objXL.ActiveWorkBook.WorkSheets("IBRENTAL")
objWb.Activate ' not absolutely necessary (for CSV)

'WScript.Echo "worksheet imported"


objxl.columns("B:B").numberformat="0000000"
objxl.columns("C:C").numberformat="00000"
' turn of those annoying warning messages
OBJXL.DISPLAYALERTS = fALSE

'wscript.echo savename

' xlWorkbookNormal
objxl.ActiveWorkbook.SaveAs savename, &HFFFFEFD1


objXl.Quit()

Set objXL = Nothing
Set objWB = Nothing
Set objR = Nothing

 

[jges]

Try this (change the inputFolder and outputFolder variables as necessary).

[COLOR=blue]Option[/color] [COLOR=blue]Explicit[/color]  
[COLOR=blue]Const[/color] vbNormal [COLOR=blue]=[/color] 1  

[COLOR=blue]DIM[/color] objXL, objWb, objR  [COLOR=green]' Excel object variables[/color]
[COLOR=blue]DIM[/color] Title, Text, tmp, i, j, file, name, savename  

[COLOR=green]' create an Excel object reference[/color]
[COLOR=blue]Set[/color] objXL [COLOR=blue]=[/color] WScript.CreateObject ("Excel.Application")  

objXL.WindowState [COLOR=blue]=[/color] vbNormal  [COLOR=green]' Normal[/color]
objXL.Height [COLOR=blue]=[/color] 300  [COLOR=green]' height[/color]
objXL.Width [COLOR=blue]=[/color] 400  [COLOR=green]' width[/color]
objXL.Left [COLOR=blue]=[/color] 40  [COLOR=green]' X-Position[/color]
objXL.Top [COLOR=blue]=[/color] 20  [COLOR=green]' Y-Position[/color]
objXL.Visible [COLOR=blue]=[/color] [COLOR=blue]true[/color]  [COLOR=green]' show window[/color]


[COLOR=blue]Dim[/color] objFSO  
[COLOR=blue]Set[/color] objFSO [COLOR=blue]=[/color] CreateObject("scripting.filesystemobject")  
[COLOR=blue]Dim[/color] myFolder, inputFolder, outputFolder, objFile  
inputFolder [COLOR=blue]=[/color] "C:\Temp"  
outputFolder [COLOR=blue]=[/color] "C:\Temp\rjm"  
[COLOR=blue]Const[/color] fileExt [COLOR=blue]=[/color] ".xls"  

[COLOR=green]'change to point to your folder path[/color]
[COLOR=blue]set[/color] myFolder [COLOR=blue]=[/color] objFSO.getfolder("C:\Temp")  

	For [COLOR=blue]Each[/color] objFile [COLOR=blue]In[/color] myFolder.Files  
		If LCase(objFSO.GetExtensionName(objFile)) [COLOR=blue]=[/color] "csv" [COLOR=blue]Then[/color]  
			  [COLOR=green]'code to process csv files[/color]
			  
			savename [COLOR=blue]=[/color] objFSO.BuildPath(outputFolder, objFSO.GetBaseName(objFile) [COLOR=blue]&[/color] fileExt)  
			  
			  [COLOR=green]' Load the Excel file from the script's folder[/color]
			Set objWb [COLOR=blue]=[/color] objXl.WorkBooks.Open(objFile)  

			  [COLOR=green]' Get the loaded worksheet object[/color]
			  [COLOR=green]'Set objWb = objXL.ActiveWorkBook.WorkSheets("IBRENTAL")[/color]
			objWb.Activate  [COLOR=green]' not absolutely necessary (for CSV)[/color]
			  
			  [COLOR=green]'WScript.Echo "worksheet imported"[/color]
			  
			  
			objxl.columns("B:B").numberformat="0000000"  
			objxl.columns("C:C").numberformat="00000"  
			  [COLOR=green]' turn of those annoying warning messages[/color]
			OBJXL.DISPLAYALERTS [COLOR=blue]=[/color] [COLOR=blue]fALSE[/color]  
			  
			  [COLOR=green]'wscript.echo savename[/color]
			  
			  [COLOR=green]' xlWorkbookNormal[/color]
			objxl.ActiveWorkbook.SaveAs savename, &HFFFFEFD1  
			  
			  
		End [COLOR=blue]If[/color]  
	Next  

objXl.Quit()  

[COLOR=blue]Set[/color] objXL [COLOR=blue]=[/color] [COLOR=blue]Nothing[/color]  
[COLOR=blue]Set[/color] objWB [COLOR=blue]=[/color] [COLOR=blue]Nothing[/color]  
[COLOR=blue]Set[/color] objR [COLOR=blue]=[/color] [COLOR=blue]Nothing[/color]

 

[karelsmits]

Hey jges,

Thank you so much. It worked wonderfully. I truly appreciate it.

All the best,
karelsmits