Loop through folder of csv files, import, and save as

[mhals]

Hi,

i'm trying to loop through a folder of csv files, import them each into a separate workbook, and save as xls files into a different folder using the contents of a particular cell as the name of the xls.

i've found code for loops, but i don't have the expertise to customize for cvs files. and the save-as has been giving me fits.

any help would be appreciated!<code> <line break=""><line break=""> </line></line></code>

 

[John_w]

Try this, if I've understood you correctly.

Option Explicit

Private Sub SaveAs_Files_in_Folder()

    Dim CSVfolder As String, XLSfolder As String
    Dim CSVfilename As String, XLSfilename As String
    Dim wb As Workbook
    Dim n As Long
    
    CSVfolder = "C:\temp\excel\"        'CHANGE AS REQUIRED
    XLSfolder = "C:\temp\excel\csv\"    'CHANGE AS REQUIRED
    
    XLSfilename = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    
    If Right(CSVfolder, 1) <> "\" Then CSVfolder = CSVfolder & "\"
    If Right(XLSfolder, 1) <> "\" Then XLSfolder = XLSfolder & "\"
       
    n = 0
    CSVfilename = Dir(CSVfolder & "*.csv", vbNormal)
    While Len(CSVfilename) <> 0
        n = n + 1
        Set wb = Workbooks.Open(CSVfolder & CSVfilename)
        wb.SaveAs filename:=XLSfolder & XLSfilename & CStr(n) & ".xls", FileFormat:=xlNormal
        wb.Close False
        
        CSVfilename = Dir()  ' Get next matching file
    Wend

End Sub

The name of the .xls file to save as is picked up from Sheet1 A1 in the workbook where this code resides. Since this value doesn't change, the code appends an index number to the .xls filename to ensure that the saved filenames are different.

 

[mhals]

thank you!
this worked very well. the only issue was in this line:

wb.SaveAs filename:=XLSfolder & XLSfilename & CStr(n) & ".xls", FileFormat:=xlNormal

each file was saved as 1.xls, 2.xls, etc...

i replaced "XLSfilename & CStr" with "CSVfilename" and now i have xls files with the corresponding csv filename, which works for my purposes despite the fact they end with .csv.xls.

 

[mhals]

spoke to soon...is there some way to strip the .csv from CSVfilename before appending .xls and saving?

 

[Joe4]

Try:

wb.SaveAs filename:=XLSfolder & Left(XLSfilename,Len(XLSfilename)-4) & CStr(n) & ".xls", FileFormat:=xlNormal

 

[mhals]

very nice, thanks!