CODEWITHSUNDEEP
excelvbabatch-file
Zaki
Zaki

Reputation: 1

Running batch file via Excel VBA

I have a batch file which runs a Python script.

I can manually click to execute that batch file.

When I try to run it via Excel VBA the file does not execute.

The code in Batch file is something like this:

%cd
file.py

The batch file & the file I want to execute are in same directory.

Instead of %cd% in above code if I type in exact directory address then Excel VBA has no problem running the batch file. For example if my batch file looks something like this then there are no issues.

c:
cd c:\folder
file.py

Upvotes: 0

Views: 1356

Answers (2)

Reece Fuller
Reece Fuller

Reputation: 1

Thank you so much I was having a problem with this for a long time. It makes much more sense now. I had required all SQL in the folder to be executed. **I am new to Batch Files

Here is my Answer Example:-

Excel CommandButton Code:

Private Sub CommandButton1_Click()
Dim Username1 As String
Dim Password1 As String
Dim Server1 As String
Dim Database1 As String
Dim CommandString As String

Username1 = Cells(5, 6).Value
Password1 = Cells(6, 6).Value
Server1 = Cells(7, 6).Value
Database1 = Chr(34) + Cells(8, 6).Value + Chr(34)
CommandString = ThisWorkbook.Path & "\Place_SQL_Scripts_in_here\ExecuteAllSQLWithExcelParameters.bat" + " " + Username1 + " " + Password1 + " " + Server1 + " " + Database1
Call Shell("cmd.exe /c" & CommandString, vbNormalFocus)
End Sub

Batch File Code: @Mofi - I used a snippet of your code

@Echo off 

goto :init

:init
setlocal 
set Username1=%1
set Password1=%2
set Server1=%3
set Database1=%4
Echo.
Echo Executing All SQL Scripts in Folder "Place_SQL_Scripts_in_here"
Echo.

cd /D "%~dp0"

for %%G in (*.sql) do (echo [Executing %%G] & echo. & sqlcmd -S %Server1% -d %Database1% -U %Username1% -P %Password1% -i "%%G" & echo.)
Goto :End

:End
Echo Completed
Echo.
PAUSE
endlocal
goto :EOF

I had also received assistance from this post (How to execute Batch File while passing parameters from Excel)

Upvotes: 0

Mofi
Mofi

Reputation: 49086

Use the following code in batch file:

@echo off
cd /D "%~dp0"
python.exe file.py

It would be even better to specify python.exe with full path if the batch file is executed always on same machine with installation location of Python known.

The command cd /D "%~dp0" sets current directory to directory of the batch file even if it is on a different drive than current drive. Run in a command prompt window cd /? and call /? for details.

A *.py file is not an executable. It is a Python script which needs a Python interpreter for doing something. Therefore it is better to explicitly run the Python interpreter with the script file as parameter than letting Windows find out via Windows registry which application to use to Open a *.py file which results in execution of the script if python.exe is the application registered for Open action.

Upvotes: 2

Related Questions