Runnig a .bat file from within VB

Question

In Win 10 while running an Excel program, I have a button which executes a VB. module. In the module is the statement Process.Start ("Batfile.bat"). Batfile.bat is in the same directory as the Excel file. Batfile's contents are simply

pause

msg * List Created!

Here is the full code:

Sub Worksheets_to_txt()
'<--Saves each worksheet as a text file with the same name

    Dim CalcState As Long
    Dim EventState As Boolean
    Dim PageBreakState As Boolean
    
    Application.ScreenUpdating = False

'    EventState = Application.EnableEvents
'    Application.EnableEvents = False

    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual

    PageBreakState = ActiveSheet.DisplayPageBreaks
    ActiveSheet.DisplayPageBreaks = False


    Dim ws As Worksheet
    Dim relativePath As String
    Dim answer As VbMsgBoxResult

    relativePath = ActiveWorkbook.Path
    
'    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export

    Process.Start ("Batfile.bat")

'    If answer = vbYes Then

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets

        ws.Select
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        relativePath & "\" & ws.Name & ".txt", _
        FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close
        ActiveWorkbook.Activate
    Next

    Worksheets("Master").Activate
    MsgBox "Text files have been created."
'     End If
    
    ActiveSheet.DisplayPageBreaks = PageBreakState
    Application.Calculation = CalcState
'    Application.EnableEvents = EventState
    Application.ScreenUpdating = True

End Sub

This runs fine when I execute it outside of Excel by itself, but pushing the button in excel gives me

Run-time error "424":

Object Required

I have tried a number of online remedies, but I keep getting the same result. What am I doing wrong?

Your error comes from VBA code. And probably not related to `Process.Start` — T.S., Aug 18 '20 at 20:18
`I have a button which executes a VB. module. In the module is the statement Process.Start ("Batfile.bat")` can you share this code? — Trevor, Aug 18 '20 at 20:21
I have commented out everything in the VB module other than Process.Start ("Batfile.bat"). The results are still as I described. The contents of Batfile are not what I ultimately intend, but just something simple that allows me to describe the problem. — CPaul, Aug 18 '20 at 20:28
What do you mean by "execute a VB module"? Do you have VBA code that executes VB.NET code, which `Process.Start` is? Like noted above, your error indicates that you don't have VB.NET code at all, and trying to execute `Process.Start` as if it belonged to VBA, which it doesn't. — GSerg, Aug 18 '20 at 20:36
The module I refer to is a VB macro in the excel file. Everything else in it works except for Process.Start... Here is the complete code: — CPaul, Aug 18 '20 at 20:53
When I try to post the full code, I'm told it's too long by 904 characters.\ — CPaul, Aug 18 '20 at 20:56
@CPaul in the original post? This will happen if you try and post it as a comment, don't do that, [*edit*](https://stackoverflow.com/posts/63475938/edit) your original post. — Trevor, Aug 18 '20 at 20:58
`The module I refer to is a VB macro in the excel file` - then it is a VBA module. You cannot put VB.NET code into a VBA code module. — GSerg, Aug 18 '20 at 20:59
Possible duplicate of [How do you run a .exe with parameters using vba's shell()?](https://stackoverflow.com/questions/20917355/how-do-you-run-a-exe-with-parameters-using-vbas-shell) — GSerg, Aug 18 '20 at 21:00
1. if you remove `process.start`, what happens? 2. try this https://forums.asp.net/t/1812974.aspx?Start+process+and+wait+for+it+to+complete+in+VBA — T.S., Aug 18 '20 at 21:28
Progress from the link you recommended - Call Shell("C:\directory1\directory2\Batfile.bat") worked. — CPaul, Aug 18 '20 at 21:50
But is there a way to not have to specify the absolute path so that it is assumed that the file in the same directory as the excel file? — CPaul, Aug 18 '20 at 21:59
Thanks for the suggestion. I had tried it. I also tried:something similar before. Neither it nor your suggestion worked. What — CPaul, Aug 19 '20 at 01:41
@CPaul That would mean you didn't try it correctly. Please show how you tried. — GSerg, Aug 19 '20 at 06:42
Call Shell("C:\Users\DTKB68\OneDrive - Zebra Technologies\DesktopSynced\Batteries\I2C battery terminal Communication\New folder\New folder\Sandbox\\BatFile.bat", vbNormalFocus) I cannot get the command to work with a relative path, however. Can you help with that? — CPaul, Aug 19 '20 at 13:55

score -1 · Answer 1 · answered Aug 19 '20 at 14:03

-1

System.Diagnostics.Process.Start() should work ideally. Please try by providing absolute path.

answered Aug 19 '20 at 14:03

Seethalaxmi moorthy

1

I couldn't get Process.Start( ) to work with an absolute or relative path. I could get Call Shell() to work with an absolute path but not a relative one. How do I get it to work with a relative path? – CPaul Aug 19 '20 at 14:07
The above is true with Process.Start() as well as System.Diagnostics.Process.Start(). Neith absolute nor relative references work. Absolute path works with Call Shell() but not relative, which is what I am looking for. – CPaul Aug 19 '20 at 14:37
I have launched an excel program that uses a Visual Basic macro. Within the macro, I would like to find the directory path of the excel program. CurDir() gives me some default directory, and ActiveWorkbook.path gives me a URL beginning with https: How do I obtain the directory of the excel program? Strangely, the code I posted in my original post does store worksheets as text files in the excel program directory. – CPaul Aug 19 '20 at 16:49
@CPaul On top of refusing to realize that VBA and VB.NET are completely different languages, and thus you cannot use Process.Start from VBA, you have also withheld the very important information that your `.Path` returns a URL. If you are storing the file on Onedrive, then see https://stackoverflow.com/q/46346567/11683. – GSerg Aug 20 '20 at 07:04
Thank you for your help. But you attribute intransigence on my part to simple ignorance, and the belated discovery of information to the willful withholding of it. Please allow me to plead that I am neither so stubborn nor so secretive. – CPaul Aug 20 '20 at 13:29
OK, more progress.The result of the following is C:\users\DTKB68\One Drive - Zebra Technologies\Documents\I2CFileCreate.xlsm – CPaul Aug 20 '20 at 14:12
Dim fpath As String Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") fpath = fso.GetAbsolutePathName(ThisWorkbook.Name) MsgBox fpath – CPaul Aug 20 '20 at 14:12
But the files is not in the folder Documents, nor is Documents in its path. – CPaul Aug 20 '20 at 14:13
I thought I could simply use string manipulation functions to assemble the proper answer from the above response and relativePath = ActiveWorkbook.path. But the the string functions do not appear to work with variables, only with constants. Can you suggest a solution? – CPaul Aug 20 '20 at 14:15
Nevermind, I seem to have things working. Thanks for your help so far. – CPaul Aug 20 '20 at 14:41

Runnig a .bat file from within VB

1 Answers1