Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Run Batch file inside the selected folder

    Hi,

    I import multiple xml files via this script. Once i select the folder for xml files to append i want to run a batch file. The batch script renames the files inside the folder. Then to continue with this vba codes to append the xml files.

    Anyways how to do this.

    Code:
    Sub ListFiles()
    Dim ShellApplication As Object
    Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    Path = ShellApplication.self.Path
    Set ShellApplication = Nothing
    [a3] = "XML"
    [b3] = "Files"
    Call ListMyFiles(Path, True)
    End Sub
    Sub ListMyFiles(mySourcePath, IncludeSubfolders)
        Set MyObject = New Scripting.FileSystemObject
        Set mySource = MyObject.GetFolder(mySourcePath)
        Application.ScreenUpdating = False
        For Each myfile In mySource.Files
            If Right(myfile.Name, 3) = "XML" Or Right(myfile.Name, 3) = "xml" Then 'IS XML?
                LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
                ActiveWorkbook.XmlImport URL:=mySource & "\" & myfile.Name, _
                    ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$" & LastRow + 1)
                 maps = ActiveWorkbook.XmlMaps.Count
                For i = 1 To maps
                    ActiveWorkbook.XmlMaps(1).Delete
                Next i
            End If
        Next
        If IncludeSubfolders Then 'SEARCH SUBFOLDERS FOR SAME CRITERIA
            For Each MySubFolder In mySource.SubFolders
                Call ListMyFiles(MySubFolder.Path, True)
            Next
        End If
        'End With
        Application.ScreenUpdating = True
    End Sub
    Batch file

    Code:
    Sub renaming()
        Call Shell(Environ$("COMSPEC") & " /c C:\readrename.bat", vbNormalFocus)
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Why don't you rename the files as part of your VBA? Saves running extra files that may not be available.

    cheers, Paul

  3. #3
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    Why don't you rename the files as part of your VBA? Saves running extra files that may not be available.

    cheers, Paul

    Hi Paul,

    Well said Paul, if you extend me help, this is scenario the folder contains multiple files and they are infact the response files to xml files.
    File names: EXEC_19112013_111239_01_1.XML.1.19112013.PASS
    EXEC_19112013_162753_01_1.XML.1.19112013.PASS

    Description of file naming: XXXX_ddmmyyyy_HHMMSS_01_1.XML..1.19112013.PASS

    Remove the trailing .1.19112013.PASS which is not required, and converts the file format to text type.

    Thanks in advance.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    If the files you need to use all end in ".PASS" you can change the 11th line as follows:
    Orig: If Right(myfile.Name, 3) = "XML" Or Right(myfile.Name, 3) = "xml" Then 'IS XML?
    New: If Ucase(Right(myfile.Name, 4)) = "PASS" Then 'IS XML?

    You need to decide what to do with the files once you have imported them. Maybe rename them to filename.imported?

    cheers, Paul

  5. #5
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Paul, It's amazing,amazing i never thought it.

    Thanks.

Posting Permissions

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