How to quickly * convert many .txt files to .xls files

Question

Update: I just found out that someone with a more powerful server would be working on a task that was assigned to me, so it's great that I did not make this program fast enough. However, the answer below (Excel Automation) helped make the program three times faster, so I would recommend it to someone with fewer (but many more) files.

I am trying to convert many (over 300,000) .txt files to .xls files. I found out how to do it here:

Batch convert TXT to XLS using VBA

But it is very slow (after an hour, it only converted ~ 200 of our 300,000 files), although the files are not so large.

I tried to speed it up by disabling ScreenUpdating, but I was not able to disable ScreenUpdating successfully. Can someone explain where in ScreenUpdating is turned off so that my code works faster? Or, better yet, any ideas for a more effective program?

Here is the code:

Sub TXTconvertXLS() 'Variables Dim wb As Workbook Dim strFile As String Dim strDir As String Application.ScreenUpdating = False 'Directories strDir = 'path went here strFile = Dir(strDir & "*.txt") 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 Application.ScreenUpdating = True End Sub 

Answer 1

A few options that should be faster.

• Use Powershell (saves the code below in Notepad, for example, "xx.ps1", update the source directory and run it)
• Excel automation in a hidden instance, not in the current one.

Powershell

Relying on https://superuser.com/questions/875831/using-powershell-is-it-possible-to-convert-an-xlsx-file-to-xls and Using Powershell to cycle Excel files and check for an electronic name tables

 $files = Get-ChildItem C:\Temp\*.txt Write "Loading Files..." $Excel = New-Object -ComObject Excel.Application $Excel.visible = $false $Excel.DisplayAlerts = $false ForEach ($file in $files) { $WorkBook = $Excel.Workbooks.Open($file.Fullname) $NewFilepath = $file.Fullname -replace ".{4}$" $NewFilepath = $NewFilepath + ".xls" $Workbook.SaveAs($NewFilepath,56) } Stop-Process -processname EXCEL $Excel.Quit() 

Excel Automation

 Sub TXTconvertXLS2() Dim objExcel As Excel.Application Dim wb As Workbook Dim strFile As String Dim strDir As String Set objExcel = New Excel.Application With objExcel .Visible = False .DisplayAlerts = False End With 'Directories strDir = "c:\temp\" strFile = Dir(strDir & "*.txt") 'Loop Do While strFile <> "" Set wb = objExcel.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 objExcel.DisplayAlerts = False objExcel.Quit Set objExel = Nothing End Sub 

Answer 2

I tried to change the tool. Without luck, could you please indicate where my failure is?

I need the 4,5,6,7 column to be imported as text, otherwise many hexadecimal values ​​are converted to scientific ones :)

I used OpenText instead of Open.

 Sub TXTconvertXLS2() Dim objExcel As Excel.Application Dim wb As Workbook Dim strFile As String Dim strDir As String Set objExcel = New Excel.Application With objExcel .Visible = False .DisplayAlerts = False End With 'Directories strDir = "c:\temp\" strFile = Dir(strDir & "*.txt") 'Loop Do While strFile <> "" wb = objExcel.Workbooks.OpenText((strDir & strFile), , , xlDelimited, , True, True, , , , , , Array(Array(4, 2), Array(5, 2), Array(6, 1), Array(7, 2)), , , , , "|") 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 objExcel.DisplayAlerts = False objExcel.Quit Set objExel = Nothing End Sub