Macro Batch Save As

[JSherrod]

Is there a macro to batch save many xls files to text (prn) files?

[Ivan F Moala]

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

[JSherrod]

Thanks! Worked great. Exported 718 xls files.

[dgr7]

hello,
I'm trying to deal with the same problem as the original post in this thread, where I have a large number of workbooks in a directory and I want to batch convert them to .prn files.
I cut & pasted the above VBA code into Excel and tried to run it, but I immediately get the error Combile error: Variable not defined and the word
msoFileTypeAllFiles
of the line
.FileType = msoFileTypeAllFiles
is highlighted. I looked through the code and don't see it mentioned anywhere else or Dim'd etc.
Why did the code work fine for JSherrod in this thread but not me?
The only change I made to the code is the line
Const Drive As String = "C:AData"
to
Const Drive As String = "D:\BDcd\The Ct Bnk"

I'm using Excel 2002, btw.
If I can't get this code working, is there some other VBA code out there that would do the same job?

thanks,
david

[dgr7]

anyone? help?

thanks, david

[Datsmart]

I'll bet you need to set a reference in VBA.
Like Tools | References...
Microsoft Visual Basic for Applications Extensibility...

Might be a different reference though.

[Greg Truby]

John (Datsmart) is correct, it is a referencing error that you'll fix in the VBE menu option Tools | References... The reference you'll need is to Microsoft Office xx.x Object Library (the "mso" at the front of the variable names lets you know this). [You would also see this in the Object Browser, but only if you have a reference to Office -- sort of a Catch 22.]

[dgr7]

Datsmart & Gret Truby:
Thank you both! I turned on both of those References and now the code works!
One quick question:
In the line
MsgBox "Completed - " & dCount & " files have been converted.", 4160,
"Batch Info"
What is the 4160 referring to?

david

[Datsmart]

It assigns the type of Message Box to display.
In this case one with the Information icon (64) and the msgbox is systemModal (4096), all applications are suspened until a response.
Added together, they are 4160.

[Greg Truby]

4160 is the sum of

[*]vbSystemModal (4096 / &H1000)[*]vbInformation (64 / &H40).[/list]You can see this in the Object Browser if you look up the VbMsgBoxStyle contants in the VBA library.