How can I batch open a txt file in excel 2003 and save back as a .

[Guest]

I have about 3000 .txt files I need to open in excel and just save them. Is
there a way I can batch run these? It would involve opening the txt file and
saving it as the same name.

 

[Jason Morin]

Try this macro. Change "MyFolder" to the actual path. To
use, open up a new workbook, press ALT+F11, go to Insert

Module, and paste in the code. Press ALT+Q, go to Tools
Macro > Macros and run it.

Sub ChangeXLS()
'Constructive criticism from VBA programmers appreciated
Dim MyFolder As String
Dim NewName As String
Dim OldName As String
Dim patharray As Variant
MyFolder = "C:\Program Files\ztest"
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.SearchSubFolders = False
.Filename = "*.txt"
.FileType = msoFileTypeAllFiles
Application.DisplayAlerts = False
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
patharray = Split(.FoundFiles(i), "\")
OldName = patharray(UBound(patharray))
NewName = Application.WorksheetFunction. _
Substitute(OldName, ".txt", ".xls")
Workbooks.Open Filename:=MyFolder _
& "\" & OldName
With ActiveWorkbook
.SaveAs Filename:=MyFolder & "\" & NewName
.Close
End With
Next
Else
MsgBox "There were no files found."
Exit Sub
End If
Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
End Sub

 

[Myrna Larson]

And, in case FileSearch doesn't work correctly (it doesn't for me in XL-XP --
returns no files when there ARE matching files), in the past I've posted code
to retrieve a list of files in a directory. So has Bill Manville. If you can't
find it on Google, I can re-post.

 

[Dave Peterson]

Just some thoughts....(and avoiding the problem that .filesearch poses in some
versions of windows).

Option Explicit
Sub ChangeXLS()
'Constructive criticism from VBA programmers appreciated
Dim MyFolder As String
Dim NewName As String
Dim i As Long '<-- you missed this one!
Dim Wkbk As Workbook

'MyFolder = "C:\my documents\excel\test"
MyFolder = "C:\Program Files\ztest"
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.SearchSubFolders = False

'from what I've read, this is probably more robust
'across all versions of windows
'(instead of *.txt)
.Filename = ".txt"

.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
'since you're looking at .txt files, just chop it
NewName = Left(.FoundFiles(i), _
Len(.FoundFiles(i)) - 4) & ".xls"
Set Wkbk = Workbooks.Open(Filename:=.FoundFiles(i))
With Wkbk
Application.DisplayAlerts = False
.SaveAs Filename:=NewName, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
.Close savechanges:=False
End With
Next
Else
MsgBox "There were no files found."
'might as well let the code finish
'and reset all your stuff (.screenupdating in this case)
'Exit Sub
End If
End With
Application.ScreenUpdating = True
End Sub

Application.worksheetfunction.substitute() is case sensitive. If your filename
were asdf.TxT, then you wouldn't get your replaced .xls.

..foundfiles(i) will return the fully qualified filename. And since you're
saving to the same location, you don't need to extract the filename and later
rebuild it.

I like to turn off error checking/.displayalerts for as little time as
possible. And right near the lines that I want.

And I think I wouldn't leave it to excel to guess what fileformat I wanted. It
doesn't hurt to specify it and it makes me feel better. (Same thinking with
savechanges:=false.)

And I like using a workbook variable to hold the newly opened workbook (.txt
file).

And I think I would probably use Workbooks.OpenText to be able to specify how to
import each field.

Then this:
Set Wkbk = Workbooks.Open(Filename:=.FoundFiles(i))
With Wkbk

Would look more like:

Workbooks.OpenText Filename:=.foundfiles(i), Origin:=437, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)
Set wkbk = activeworkbook
with wkbk

When I post routines like this, I'll suggest that they open one .txt file
manually with the recorder on. Then they can plop that portion of their
recorded macro into this section.