How to run a python script using VBA

Asked

Viewed 70 times

0

I created a project that consists of sending automatic messages through the Telegram API, I did all this part in Python and works perfectly, as shown below.

import requests
from datetime import date
import time

#TELEGRAM INFO
MyToken = "meuToken"
MyChatID = "-meuId"

#--------------------------- SEND TEXT TO TELEGRAM ---------------------------
DHoje = date.today() 
DHoje = DHoje.strftime("%d/%m/%Y")
# MY TEXT
MyMsg = "Banco de Dados" + " " + DHoje
#SEND METHOD
SendMsg = requests.post("https://api.telegram.org/bot{}/sendMessage?chat_id={}&text={}".format(MyToken, MyChatID, MyMsg))
time.sleep(3)

The big issue is that I need to run this script through a code in VBA, and I can even do this, but despite the screen blink and open quickly, no message is sent. Follow the VBA code part:

Option Explicit

Public Const PythonPath As String = """C:\meucaminho\Python36\python.exe"""
Public Const MyFilePath As String = """C:\meucaminho\MsgFileTelegram.py"""

Public Sub ChamarPython()

    Call VBA.Shell(PythonPath & " " & MyFilePath)

End Sub

Obs.: When I run the python code outside the excel macro everything works perfectly, but when I run the script by VBA nothing. Any suggestions? Note: I read a lot about the subject and gave a lot google, but I could not walk alone.

python vba excel-vba python-requests

  • User has permission in folder/program (C:\meucaminho\MsgFileTelegram.py)?

  • Hi, @Paulomarques, the user has permission yes. I am available for any other questions.

  • When running python, try to run a batch (.bat) in the same folder. This batch should only have commands dir and pause. If it works, try to make the batch call python, keep the pause, because if you make a mistake, you will be able to read it.

  • @Paulomarques, I did as you suggested and happened the same, the screen flashes but the . bat does not perform. But clicking on the BAT wheel normal. I’m not sure, but it seems to me that the command Call is not passing the path of the file to the . exe of python/terminal. Any idea? From now on I appreciate the commitment to help.

  • I don’t know much about VBA, but I think you can only pass with Shell(PythonPath & " " & MyFilePath), that is, removing the Call VBA.

  • Unfortunately it didn’t work either, the same problem happened. Python Shell opened and closed soon after. It flashed on the screen, but it didn’t turn.

Show 1 more comment

1 answer

0

Try this:

RetVal = Shell(PythonPath & MyFilePath)

Or if the Python script is in the same directory as the Excel workbook, you can try:

RetVal = Shell(PythonPath  & ActiveWorkBook.Path & "\MyFilePath)

Still a third alternative...

Sub Python()

    Dim objShell As Object
    Dim PythonPath, MyFilePath As String

    Set objShell = VBA.CreateObject("Wscript.Shell")

    PythonPath= "C:\meucaminho\Python36\python.exe"
    MyFilePath= "C:\meucaminho\MsgFileTelegram.py"

    objShell.Run PythonPath & MyFilePath

End Sub
  • 1

    Paul, thank you for your willingness to help! I tested what you suggested, in the first two options I needed to define the RetVal As Variant, I don’t know if it was your idea, but it didn’t work. A 3 suggestion also implemented and could not run the Python Script, the shell opens and immediately closes, not running the python code.

  • OK I’ll review the options and edit the answer

Browser other questions tagged python vba excel-vba python-requests

You are not signed in. Login or sign up in order to post.