Convert all .txt files to .xls in folder

Hi all,

I'm trying to write a VBA macro that opens all .txt files in a folder and saves them as .xls with the same name. I've found some code online that has some of the same functionality but they use Application.Filesearch which is no longer available on Excel 2010.

I found an example using the Dir function but am having trouble getting it to work.

Thanks for any help you can offer.

Sub LoopAllFiles()

sPath = "S:\Test loop macro\files"

sDir = Dir$(sPath & "*.txt", vbNormal)

Do Until LenB(sDir) = 0

Set oWB = Workbooks.Open(sPath & sDir)

    ActiveWorkbook.SaveAs , FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

oWB.Close

sDir = Dir$

Loop
End Sub

Hi Natalie, welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature)

Sub MyMacro()
   Code
      Code
   Code
End Sub

Thanks for fixing that, we can offer some ideas on this.

Hi Natalie

You need a back slash in this line of code

sPath = "S:\Test loop macro\files\"

'CSVs To Workbooks
I have a macro that may be "ready to use" for converting all files of a specified type, like CSV or TXT, in a specified folder into Excel files. There's a workbook included to run the macro from where you indicate your preferences on the SETUP sheet.

CSVs to Workbooks
(or other file types to Excel Workbooks)

The same functionality of JB's code but somewhat more compact:

Sub CSVsToWorkbooks()
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
  
 c00 = "E:\OF\"
 c01 = Dir(c00 & "*.txt")

 Do While c01 <> ""
  With Workbooks.Open(c00 & c01)
   .SaveAs c00 & .Sheets(1).Name & ".xls", xlNormal
   .Close
  End With
  c01 = Dir
 Loop
 
 if dir(c00 & "old",16)="" then MkDir c00 & "old\"
 CreateObject("scripting.filesystemobject").movefile c00 & "*.txt", c00 & "\old"
End Sub

PS. @JB

I don't like 'killing'

PS. @JB

I don't like 'killing'

The file is flexible and entry-level editable, more aimed at ease of use and readability. Killing (deleting) the source file(s) when done is only one of the options offered. Your dislike of killing is understandable, others would simply opt to not use that option if they feel the same way. The benefit of having the option, though, should be evident.

On the other hand, I do pick up things from your brevity code, so keep it up!

@JB

"Killing' wasn't so much on your VBA code, but in a more pacifistic sense.

I posted my answer primarily to draw your attention to:

- the name that is being given automatically to the first sheet
- the use of that name to name the file
- the method movefile (using *)

Considering your post, you noticed