On 2002-03-29 05:45, JSherrod wrote:
Is there a macro to batch save many xls files to text (prn) files?
Hi JSherrod
What I would suggest you do is to place all
the files you want to convert into a seperate
Directory and then run this code.
Note: You will have to change the Const
Const Drive As String = "C:AData"
to the directory you want to do the batch
conversion to.
Option Explicit
Dim i As Integer
Dim sChFiles() As String
Dim iWB As Integer
Dim dCount As Double
Const Drive As String = "C:AData"
Sub Version1()
With Application.FileSearch
.NewSearch
.LookIn = Drive
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim sChFiles(.FoundFiles.Count)
For i = 1 To .FoundFiles.Count
sChFiles(i) = .FoundFiles(i)
Next
End If
If .FoundFiles.Count = 0 Then MsgBox "No xls files in " & Drive: End
End With
On Error GoTo ErrH
Application.ScreenUpdating = False
Application.DisplayAlerts = False
dCount = 0
For iWB = 1 To UBound(sChFiles())
Workbooks.Open Filename:=sChFiles(iWB)
With ActiveWorkbook
.SaveAs Filename:=Left(sChFiles(iWB), Len(sChFiles(iWB)) - 3) & "prn", _
FileFormat:=xlTextPrinter
.Close False
dCount = dCount + 1
End With
Next
Application.ScreenUpdating = True
MsgBox "Completed - " & dCount & " files have been converted.", 4160, "Batch Info"
Exit Sub
ErrH:
If Err.Number <> 1004 Then
MsgBox Err.Number & " :=" & Err.Description
Else
Resume Next
End If
End Sub
HTH
Ivan