VBA converting .csv files to .xlsx in a new location.

[BrianG86]

Hi,

I have a folder full of files that are extracted from our in house system on a daily basis into a .csv file.

What I need to do is have a code that will convert these all to .xlsx files into a new location every morning.

I don't even know where to start with this. Can anyone help?

Thanks

 

[paddydive]

Try this

Change your CSV file folder and excel file folder as per need

Sub CSVtoXls()


 Dim CSVfolder As String
 Dim XlsFolder As String
 Dim fname As String
 Dim wBook As Workbook
 
 CSVfolder = "C:\csvfolder\"
 XlsFolder = "C:\xlsFolder\"
 
 fname = Dir(CSVfolder & "*.csv")
 
 Do While fname <> ""
    Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",")
    wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), ThisWorkbook.FileFormat
    wBook.Close False
 fname = Dir
 Loop
 
End Sub

 

[BrianG86]

Hi,

Thanks for the reply. This is not working for me. I am new to this, so is there anything I need to amend on the above code?

I have input it as below:

Sub CSVtoXls()



 Dim CSVfolder As String
 Dim XlsFolder As String
 Dim fname As String
 Dim wBook As Workbook
 
 CSVfolder = "T:\08. CaseBlocks Data for Reports\Stephanie"
 XlsFolder = "C:\UserData"
 
 fname = Dir(CSVfolder & "*.csv")
 
 Do While fname <> ""
    Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",")
    wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), ThisWorkbook.FileFormat
    wBook.Close False
 fname = Dir
 Loop
 
End Sub

 

[paddydive]

if I guess it correctly this is where the problem is :

CSVfolder = "T:\08. CaseBlocks Data for Reports\Stephanie"
XlsFolder = "C:\UserData

Change is by this --- Just a "\" added at last position

CSVfolder = "T:\08. CaseBlocks Data for Reports\Stephanie\"
XlsFolder = "C:\UserData\

 

[gabymancero]

Hi, also note that if you would like to convert your files to a specific format then you need to change:

wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), ThisWorkbook.FileFormatTO
wBook.SaveAs XlsFolder & Replace(fname, ".csv", ".xls")</pre></pre>
so the modified version will read:

Sub CSVtoXls() Dim CSVfolder As String Dim XlsFolder As String Dim fname As String Dim wBook As Workbook CSVfolder = "C:\csvfolder\" XlsFolder = "C:\xlsFolder\" fname = Dir(CSVfolder & "*.csv") Do While fname <> "" Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",") wBook.SaveAs XlsFolder & Replace(fname, ".csv", ".xls") wBook.Close False fname = Dir Loop End Sub</pre>

 

[JoeCJ]

It works, but the result files are still saved as .csv, and could not be opened directly in excel, any thought? Thanks. I changed the .xls to .xlsx

 

[hdc0ny]

Try this

Change your CSV file folder and excel file folder as per need

Sub CSVtoXls()


 Dim CSVfolder As String
 Dim XlsFolder As String
 Dim fname As String
 Dim wBook As Workbook
 
 CSVfolder = "C:\csvfolder\"
 XlsFolder = "C:\xlsFolder\"
 
 fname = Dir(CSVfolder & "*.csv")
 
 Do While fname <> ""
    Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",")
    wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), ThisWorkbook.FileFormat
    wBook.Close False
 fname = Dir
 Loop
 
End Sub

===================================================

Hello MrExcel:
I'm new on this site, I found this code that i will like to use it, my question is : How I can change the 'XlsFolder' to use a Browser for a folder location?.

 

[MarkCBB]

Helpful post, Thank you