.txt -> .xls format

[SES]

Hi,

I need to automatize the conversion of ''.txt'' files to ''.xls''.

For this purpose, I Have made the following macro :

Sub GetSheets()
Path = "C:\Users\...\"
Filename = Dir(Path & "*.txt")
Do While Filename <> ""
Workbooks.Open Filename
ActiveWorkbook.SaveAs Filename, FileFormat:=xlNormal
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


For a reason I ignore, once the macro is finished, the files are always in .txt instead of .xls...

Would you help me solve this issue plz ?

Thx

[lynnnow]

use this
ActiveWorkbook.SaveAs Replace(Filename,".txt", ".xls", 1, , vbTextCompare)

If you are using Excel 2007 or above, change the ".xls" to ".xlsx" if it is a regular Excel workbook, otherwise you can use ".xlsm" or ".xlsb" to save it to the other formats.

[SES]

Helo Lynnow,

thank you for the script, however It is still not working..

Sub GetSheets()
Path = "C:\\"
Filename = Dir(Path & "*.txt")
Do While Filename <> ""
Workbooks.Open Filename
ActiveWorkbook.SaveAs Replace(Filename, ".txt", ".xlsb", 1, , vbTextCompare)
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

May you try it and tell me the reason plz ?

Tx





use this
ActiveWorkbook.SaveAs Replace(Filename,".txt", ".xls", 1, , vbTextCompare)

If you are using Excel 2007 or above, change the ".xls" to ".xlsx" if it is a regular Excel workbook, otherwise you can use ".xlsm" or ".xlsb" to save it to the other formats.

[ZVI]

Try this:


Sub TxtToXlsb()
Const Path = "C:\Users\...\" ' <-- change to suit
Const Mask = "*.txt"
Dim Filename As String
Application.ScreenUpdating = False
Filename = Dir(Path & Mask)
While Filename <> ""
With Workbooks.Open(Path & Filename)
.SaveAs Path & Replace(Filename, ".txt", ".xlsb"), FileFormat:=xlExcel12, CreateBackup:=False
.Close
End With
Filename = Dir()
Wend
Application.ScreenUpdating = True
End Sub

[SES]

Wow ..

Many Thx ZVI !!

SES

[ZVI]

Glad to know it has helped!
:beerchug:

[SES]

:beerchug: