How to Run a Batch File Using VBA in Excel 2013

[Macroman99]

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!

[Logit]

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.

[Macroman99]

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.

[Logit]

https://vbaf1.com/file-directory/remove-directory-and-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

[Macroman99]

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?

[Macroman99]

does not work as indicated in https://vbaf1.com/file-directory/remove-directory-and-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

[Logit]

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 ?

[Logit]

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.

[Macroman99]

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.

[Logit]

You are welcome.

[snb]

Why did you choose 'macroman' as an alias ?

[snb]

This macro suffices:


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