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

mhals

New Member
Joined
Jul 23, 2009
Messages
7
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>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try this, if I've understood you correctly.
Code:
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.
 
Upvote 0
thank you!
this worked very well. the only issue was in this line:
Code:
wb.SaveAs filename:=XLSfolder & XLSfilename & CStr(n) & ".xls", FileFormat:=xlNormal
each file was saved as 1.xls, 2.xls, etc...

i replaced "XLSfilename & CStr(n)" 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.
 
Upvote 0
spoke to soon...is there some way to strip the .csv from CSVfilename before appending .xls and saving?
 
Upvote 0
Try:

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

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top