Looping through all files in a folder - save as xlsx

[Emse_]

Apologies for a (I think) very basic question. I know this has been answered many times in many places, but the solutions I've found are rather sophisticated, well beyond my understanding at this stage.

I am looking to save all csv files in a folder in xlsx format. I have a macro (in a workbook that is open) that does this fine for a file with known name & extension. I'd like to avoid just copying this command many times and changing the name.

The command I have is

Please Login or Register  to view this content.

my files are all in the format output#.csv (where # is just a number). There are no other csv files in the folder, so I don't have to worry about matching the textstring - just want to loop through all the csv files. The number of files in the folder varies, so if I can avoid using a for loop and setting a string like outputX.csv where I iterate on X that'd be nice, but if necessary can do that.

I'm very new to VBA and needing a quick solution to a problem, so I'm not looking for anything sophisticated, just the most straightforward way to create the loop, either through all files (I've seen things like Do While name <> "" but haven't managed to adapt to get this working - yet) or set the string based on an iterator that I can manually set each time if necessary. At the moment I'm looking for something I can understand rather than a faster/better way of doing things (I can get to that when I have some time to start learning this programming properly).

Thanks

[AlphaFrog]

Try this. Change the file path and file name pattern to suit.

Please Login or Register  to view this content.

[Trebor76]

Here's my attempt:

Please Login or Register  to view this content.

Regards,

Robert

[Emse_]

Thanks AlphaFrog, especially for taking the time to explain each of the steps - it's helped explain what I wasn't understanding in other solutions I'd seen, and I've even managed to make small changes for another problem I had (small achievement I know)

thanks also Robert, I'll look through your solution as well when I have a little more time.

Emse

[Andrea Schanz]

AlphaFrog - Your solution works great for me, with one exception. It is saving the excel files into my January folder instead of the originating April folder. Please advise.

Sub Convert_CSV_Files()


Application.DisplayAlerts = False

'Declare Variables
Dim strPath As String, strFile As String, counter As Long

strPath = "\\tundra\departments\Retail_Managers\Budget Reports\EMAILED REPORTS\2014\APRIL\" 'File folder path

'File name: "output*.csv" The asterisk is a wild card
'In this case, all file names start with "output" and end with ".csv"
strFile = Dir$(strPath & "Budget*.csv") 'This gets the first file name (if any) that matches the patterm.

Do While Len(strFile) 'Loop while the file name is not blank

With Workbooks.Open(strFile) 'Open the csv file
strFile = Replace(strFile, ".csv", ".xlsx") 'Replace file extension in the file name
.SaveAs strFile, xlOpenXMLWorkbook 'Save as an .xlsx" file
.Close 'Close the .xlsx file
End With

counter = counter + 1 'Count the files converted

'Get the next csv file name.
'Uses the same pattern as the Dir function above
'Returns a blank string after the last csv file.
strFile = Dir$

Loop 'will stop when no more .csv files to open

MsgBox counter & " file(s) converted. ", vbInformation, "Conversions Complete"

End Sub

[AlphaFrog]

You changed this line and deleted the path for some reason.

Please Login or Register  to view this content.

[Andrea Schanz]

That fixed it. Thanks!!!

[AlphaFrog]

You're welcome Andrea, and well done on searching for a solution first.