Error Checking in Excel
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

Some videos you may like

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
  •