Batch convert TXT to XLS using VBA

Question

I am trying to convert a directory full of .txt files to .xls using VBA. I am using the following code:

Sub TXTconvertXLS() 'Variables Dim wb As Workbook Dim strFile As String Dim strDir As String 'Directories strDir = "\\xx\xx\xx\xx\Desktop\Test\Test1\" strFile = Dir(strDir & "*.txt") 'Loop Do While strFile <> "" Set wb = Workbooks.Open(strDir & strFile) With wb .SaveAs Replace(wb.FullName, ".txt", ".xls"), 50 .Close True End With Set wb = Nothing Loop End Sub 

The problem is this: when I run it, it immediately states that there is already a file with the name that it is trying to save in the directory. The name that it displays even has the extension .xls, even if the directory does not already have .xls! Any help would be greatly appreciated - thanks!

Answer 1

It seems you are missing strFile = Dir before Loop . Without it, you process the same TXT file.

  Do While strFile <> "" Set wb = Workbooks.Open(strDir & strFile) With wb .SaveAs Replace(wb.FullName, ".txt", ".xls"), 50 .Close False '<-already saved in the line directly above End With Set wb = Nothing strFile = Dir '<- stuffs the next filename into strFile Loop 

See Dir Function