Macro Batch Save As

[JSherrod]

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

 

[dgr7]

hello,
I have this version of the code that I first acquired from this thread and it's been working well *except* for the one issue in that in the directory that it converts all the .xls files to .prn there's also a .txt file (that I need to leave there) that I *don't* want converted but it gets converted to .prn as well? I would've thought the .Filename = "*.xls" would prevent any non-.xls files from being converted but it isn't working.....could someone let me know what I need to change in the code so it'll *only* convert .xls files and leave the .txt file alone?
thanks,
david
------------------------
Option Explicit

Dim i As Integer
Dim sChFiles() As String
Dim iWB As Integer
Dim dCount As Double

Const Drive As String = "D:\Bad Debt client data\Citizens Bank, The"

Sub TheCitizensBank_3147_ConvertAllWorkbooksInDirToPrns()
'found at http://www.mrexcel.com/board2/viewtopic.php?t=3632&highlight=batch+conversion
'then modified 7/11/2006 by dgr

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

 

[Richard Schollar]

Hi David

Try changing your line:

.FileType = msoFileTypeAllFiles

to:

.FileType = msoFileTypeExcelWorkbooks

Best regards

Richard

 

[dgr7]

that was it....thank you!!

 

[passinthru]

Well, I THOUGHT I had found what I need in this thread, but it's not working for me. I have tried the code above, but with one change - I'm tyring to convert DBF files to text. When I compile the code, it returns no errors. But if I RUN the code, it returns the following:

91 :=Object variable or With block variable not set



Debug highlights the last "End With" in the code.

Any ideas?

 

[maxbre]

generalise path save as

Hi all

I'm referring to the thread here quoted:
http://www.mrexcel.com/board2/viewtopic.php?t=3632&highlight=

I want to generalise the macro so that it is going to work in any dir you are putting the file (use of relative path);
"Const Drive As String = "Path"

and I also want to save the file as *.txt (tab delimited);
is it enough to change the extension to "txt"?

Any help?

Thank you

max

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