Macro Batch Save As - Page 2
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Macro Batch Save As

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

    Default

     
    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/viewto...tch+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

  2. #12
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi David

    Try changing your line:

    Code:
    .FileType = msoFileTypeAllFiles
    to:

    Code:
    .FileType = msoFileTypeExcelWorkbooks
    Best regards

    Richard
    Richard Schollar

    Using xl2013

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

    Default

    that was it....thank you!!

  4. #14
    Board Regular
    Join Date
    Jun 2003
    Location
    GMT -5
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #15
    Board Regular
    Join Date
    Jun 2005
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default generalise path save as

      
    Hi all

    I'm referring to the thread here quoted:
    http://www.mrexcel.com/board2/viewto...632&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

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