Consulting

Results 1 to 12 of 12

Thread: How to Run a Batch File Using VBA in Excel 2013

  1. #1

    How to Run a Batch File Using VBA in Excel 2013

    My system is a 64 bit Windows 10 Pro running Microsoft Office 2013.
    I have tried multiple methods excised from many website suggestions and all to no avail. I keep getting recurring errors. I begin with an inserted module of the sheet I want to contain the Form Control macro button in this xlsm file I am working with.
    DeleteJunk.bat file as follows:
    g:
    rmdir Testing
    To get this all to work for now I am using an empty bogus folder named Testing.
    Ultimately the folder which I want to remove is hidden and locked with the program Easy File Locker.
    Running the bat file outside Excel works fine to remove this hidden and locked folder.
    Here then is a bit of up front information to help the viewer of this post to understand the what and why.
    The xlsm file is a file my wife will open when I kick the bucket, when I buy the farm, when it is time for my dirt nap, etcetera. She is not Excel proficient in the least, but she is smart enough to pick a button that says “Upon Dwight’s Death Select This Button Before You Proceed To Do Anything.” This xlsm file is all the things she must do when I am pushing up daisies. She will not understand to go to a directory where the bat file exists and double click it to run it. She is very PC deficient.
    Here then are the many VBA code scenarios I have tried:
    Sub delete_folder()

    Dim folder As Object
    Dim path As String
    path = (“G:\Testing”)
    Set folder = CreateObject(“scripting.filesystemobject”)

    folder.deletefolder path, True

    End Sub

    Private Sub delete_folder2()
    Shell “cmd.exe /c cd “ & “F:\Dwight\In The Event Of My Death” & “ && runDeleteJunk.bat”
    End Sub


    ChDrive “F”
    ChDir “F:\Dwight\In The Event Of My Death\”
    Call Shell “F:\Dwight\In The Event Of My Death\DeleteJunk.bat”
    End Sub


    Shell (“F:\Dwight\In The Event Of My Death\DeleteJunk.bat”, vbHide)

    Shell (“F:\Dwight\In The Event Of My Death\DeleteJunk.bat”, vbNormalFocus)


    Sub DeleteJunk()
    Call Shell(“CMD.EXE /C “ & “F:\Dwight\In The Event Of My Death\DeleteJunk.bat”)
    End Sub


    Sub openBatch()
    Dim file_path As String
    file_path = “G:\DeleteJunk.bat”
    Call Shell(file_path, vbNormalFocus)

    End Sub

    Dim folderPath As String
    Dim shellCommand As String

    folderPath = Application.ActiveWorkbook.Path
    shellCommand = """" & folderPath & "" & " DeleteJunk.bat" & """"
    Call Shell(shellCommand, vbNormalFocus)



    Sub DeleteJunk()

    Dim folderPath As String
    Dim shellCommand As String

    folderPath = "F:\Dwight\In The Event Of My Death"
    shellCommand = """" & folderPath & "" & " DeleteJunk.bat" & """"
    Call Shell(shellCommand, vbNormalFocus)

    End Sub



    Sub ExecuteBATfile()
    Shell “cmd.exe /k cd ” & ThisWorkbook.Path & “&&RunMe.bat”
    End Sub


    Sub openBatch()
    Call Shell("""G:\DeleteJunk.bat""", vbNormalFocus)
    End Sub

    Sub openBatch()
    Call Shell("""G:\DeleteJunk.bat""", vbHide)
    End Sub

    In all cases depending on the specific code I get one of the following errors:
    Tried to insert images here but the "Manage Attachments" app was giving problems.

    In all cases when I select Debug then the line that is similar to what you see in yellow is what I get.
    Again, this line you see in yellow is the same line but depending on the code exactly what it states.
    Tried to insert images here but the "Manage Attachments" app was giving problems.

    So if someone needs to see the images I have available to insert then the first thing that needs to happen is the app needs to clear out incorrect images that persist even after an hour of time hence my first attempt at using this app more than an hour ago. The second thing that needs to happen is for the app to work as needed. I noticed there is no way for the user to delete incorrect images, not good!

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    543
    Location
    Not certain I can assist with your question ... BUT ... why are you focused on using BAT files when it appears what you are attempting
    to accomplish can all be done with VBA only.

  3. #3
    So not being very familiar with VBA how can this be done without a bat file? It is not important it be done with a bat file, I just thought this is the way to do it.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    543
    Location
    https://vbaf1.com/file-directory/rem...d-its-content/

    'VBA Removing specified Directory and its ContentSub VBAF1_Remove_Directory_and_its_Content()
        
        'Variable declaration
        Dim sFolderPath As String, oFSO as Object
        Dim oFSO As FileSystemObject
        
         'Define Folder Path
        sFolderPath = "C:\VBAF1\"
        
        'Check if slash is added
        If Right(sFolderPath, 1) = "\" Then
            'If added remove it from the specified path
            sFolderPath = Left(sFolderPath, Len(sFolderPath) - 1)
        End If
                
        'Create FSO Object
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        
        'Check Specified Folder exists or not
        If oFSO.FolderExists(sFolderPath) Then
        
            'Delete All Files
            oFSO.DeleteFile sFolderPath & "*.*", True
                          
            'Delete All Subfolders
            oFSO.DeleteFolder sFolderPath & "*.*", True
            
            'Display Message
            MsgBox "Specified directory has deleted.", vbInformation, "VBAF1"
        Else
            'Display Message
            MsgBox "Specified directory doesn't exists.", vbInformation, "VBAF1"
        End If
         End Sub

  5. #5
    Should I assume that this code needs to be changed to reflect my specific folder? If so what part of this code gets changed? The VBAF1 gets changed to reflect my specific folder name?
    More specifically the C:\VBAF1\ would be changed, in my case to, G:\MySpecifcFolderName\?
    In the code how does VBAF1 relate to C:\VBAF1\?
    Would the first line: 'VBA Removing specified Directory and its ContentSub VBAF1_Remove_Directory_and_its_Content() be changed in any way per my specific folder name?
    And finally, does this code also remove sub folders within the main folder?

  6. #6
    does not work as indicated in https://vbaf1.com/file-directory/rem...d-its-content/
    I get the following error:
    Compile error: User-defined type not defined. I click OK on this message box and then the second line Sub VBAF1_Remove_Directory_and_its_Content() is yellowed.
    in addition, the line oFSO As FileSystemObject gets highlighted

  7. #7
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    543
    Location
    I created an empty folder on my computer desktop ... named "Testing". Then I used the following macro :

    Option Explicit
    
    'VBA Removing specified Directory and its Content
    Sub VBAF1_Remove_Directory_and_its_Content()
        
        'Variable declaration
        Dim sFolderPath As String, oFSO As Object
        'Dim oFSO As FileSystem Object                  ' removed this line by inserting a Comment Marker [ ' ]
        
         'Define Folder Path
        sFolderPath = "C:\Users\jimga\Desktop\Testing\"   ' Note the use of the complete path to the folder
        
        'Check if slash is added
        If Right(sFolderPath, 1) = "\" Then
            'If added remove it from the specified path
            sFolderPath = Left(sFolderPath, Len(sFolderPath) - 1)
        End If
                
        'Create FSO Object
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        
        'Check Specified Folder exists or not
        If oFSO.FolderExists(sFolderPath) Then
        
            'Delete All Files
            oFSO.DeleteFile sFolderPath & "*.*", True
                          
            'Delete All Subfolders
            oFSO.DeleteFolder sFolderPath & "*.*", True
            
            'Display Message
            MsgBox "Specified directory has deleted.", vbInformation, "Testing"   'changed from VBAF1
        Else
            'Display Message
            MsgBox "Specified directory doesn't exists.", vbInformation, "Testing"    'changed from VBAF1
        End If
        
    End Sub
    The location of the folder/s you wish to delete .... are they located on your personal computer or on a business network ?

  8. #8
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    543
    Location
    Should I assume that this code needs to be changed to reflect my specific folder? If so what part of this code gets changed? The VBAF1 gets changed to reflect my specific folder name?
    More specifically the C:\VBAF1\ would be changed, in my case to, G:\MySpecifcFolderName\?
    In the code how does VBAF1 relate to C:\VBAF1\?
    Would the first line: 'VBA Removing specified Directory and its ContentSub VBAF1_Remove_Directory_and_its_Content() be changed in any way per my specific folder name?
    And finally, does this code also remove sub folders within the main folder?
    Yes, the code should be edited to reflect your specific folder.

    The part/s to edit would be the FOLDER NAME and the path to that folder.

    Yes you are correct ... G:\MySpecificFolderName\

    The example code presumes the folder VBAF1 is located on the C Drive. Therefore it is using: C:\VBAF1\ as the complete path.

    The SUB NAME can be anything you want. The author of the example code (and programmers alike) oftentimes name subs so it relates to the purpose of the sub. It permits
    easy recognition at a later time, what function the sub plays.

    The sub will delete the folder and anything that it contains.

  9. #9
    The folder I want to delete resides on my personal at home computer.
    Awesome, I just now inserted a new module, pasted in this code, ran the code and it works.
    Thank you so much.

  10. #10
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    543
    Location
    You are welcome.

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,234
    Why did you choose 'macroman' as an alias ?

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,234
    This macro suffices:

    Sub M_snb()
       CreateObject("scripting.filesystemobject").DeleteFolder "C:\Users\jimga\Desktop\Testing", True
    End Sub

Tags for this Thread

Posting Permissions

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