Macro Batch Save As

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
error in batch .xls to .prn conversion code?

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
 
Upvote 0
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.
 
Upvote 0
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.]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
4160 is the sum of <ul>[*]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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top