VBS Convert CSV in XLS for hundreds files

[vadius]

Hi

I am trying to develop a nacro to convert every *csv files I have in a directory to *xls. My pb is I can't manage to save as an *.xls file with name of the *csv files being converted.

Has anyone an idea ?

Thanks for your help


Sub CSVtoXLS()
End Sub
Dim i As Integer, wb As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "G:\VBA\201007"

'Here I have all my *csv files, format name is "nssmi_date.xls" for ex "nssmi_20100701.xls". The next day the namne of the extract will be nssmi_20100702 etc...

.SearchSubFolders = False
.Filename = "*.csv"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Range("D1").Select

'Now I want to save the converted *csv file into an exel one, but with the same name, just a different format file/extension, in the following folder.

ChDir "G:\VBA\201007\CleanExtract"

"G:\Index Arbitrage - Swaps\VBA\201007\CleanExtract\nssmi_20100701.xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


'The problem is that for each *csv file, the macro is saving with the same name (nssmi_20100701). I want the macro to save as an *.xls but with the same *csv name it just converted. I don't know how to introduce the loop.

'Then here I am just copy cut and paste a cell in each worksheet

wb.Worksheets(1).Select
wb.Worksheets(1).Range("A1").Select
Selection.Cut
Range("D3").Select
ActiveSheet.Paste
'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub

 

[AlphaFrog]

Try something like this...

ChDir "G:\VBA\201007\CleanExtract"
        
strNewName = Replace(wb.Name, ".csv", ".xls")

wb.SaveAs strNewName, FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

 

[vadius]

thanks it's perfect !

 

[vadius]

.