Macro Batch Save As
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Macro Batch Save As

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks! Worked great. Exported 718 xls files.

  4. #4
    Board Regular
    Join Date
    Apr 2006
    Posts
    225
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    Board Regular
    Join Date
    Apr 2006
    Posts
    225
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone? help?

    thanks, david

  6. #6
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    John

  7. #7
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.]
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  8. #8
    Board Regular
    Join Date
    Apr 2006
    Posts
    225
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    John

  10. #10
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com