Execute Python from VBA-CodePudding

I have a problem with my execution with my .py code by shell in VBA.

I have a .py to download a CSV of Google Trends. After, i call this .py in VBA Macro.

In Visual Studio Code, the .py execute correctly, and the CSV update the date of the file. But, executing by Shell (VBA), execute "correctly" and my file CSV date don´t updates.

VBA Code:

Sub Prueba3()

Dim exe, pth

exe = "C:\Users\sergi\AppData\Local\Programs\Python\Python310\python.exe"
pth = "C:\Users\sergi\Desktop\Python\CSVChrome4.py"

Shell "cmd.exe /k """"" & exe & """ """ & pth & """""", vbNormalFocus


End Sub

Python Code:

from pytrends.request import TrendReq
import pandas as pd
import time
startTime = time.time()
pytrend = TrendReq(hl='en-GB', tz=360)

colnames = ["keywords"]
df = pd.read_csv("C:\\Users\\sergi\\Desktop\\Python\\keyword_list.csv", names=colnames)
df2 = df["keywords"].values.tolist()
df2.remove("Keywords")

dataset = []

for x in range(0,len(df2)):
     keywords = [df2[x]]
     pytrend.build_payload(
     kw_list=keywords,
     cat=0,
     timeframe='today 12-m',
     geo='')
     data = pytrend.interest_over_time()
     if not data.empty:
          data = data.drop(labels=['isPartial'],axis='columns')
          dataset.append(data)

result = pd.concat(dataset, axis=1)
result.to_csv('search_trends.csv')

executionTime = (time.time() - startTime)
print('Execution time in sec.: '   str(executionTime))

CodePudding user response：

FYI, I'm basically copying a previous answer of mine for this one. Try debugging the line in VBA to ensure it's being quoted correctly; VBA -> cmd -> python can be a little tricky.

'Chr(34) is the double-quote character
Dim dq As String: dq = Chr(34)
Dim objShell As Object
Set objShell = VBA.CreateObject("Wscript.Shell")

pythonexe = dq & "C:\Users\sergi\AppData\Local\Programs\Python\Python310\python.exe" & dq
pythonscript = dq & "C:\Users\sergi\Desktop\Python\CSVChrome4.py" & dq

cmd = "cmd /k " & dq & pythonexe & " " & pythonscript & dq
Debug.Print "Running command (" & cmd & ")"
objShell.Run cmd

CodePudding user response：

Either define the full path and filename in the output file i.e result.to_csv('C:\path-to-file\search_trends.csv') or determine the path from within the python script. Also consider passing the filenames into the script as parameters.

Option Explicit

Sub Prueba3()

    Const Folder = "C:\Users\sergi\Desktop\Python\"
    Const Script = "CSVChrome4.py"
    Const PyExe = "C:\Users\sergi\AppData\Local\Programs\Python\Python310\python.exe"
    Dim cmd As String, args As String

    args = """keyword_list.csv"" ""search_trends.csv"""
    
    cmd = PyExe & " """ & Folder & Script & """ " & args
    Debug.Print cmd
   
    Shell "cmd.exe /k " & cmd, vbNormalFocus
    
End Sub

python test script

#from pytrends.request import TrendReq
import pandas as pd
import time, os, sys

folder = os.path.dirname(__file__)   "/"
print ("Folder="   folder)
print ("Keywords="   sys.argv[1])
print ("Results="   sys.argv[2])

startTime = time.time()
#pytrend = TrendReq(hl='en-GB', tz=360)

colnames = ["keywords"]
df = pd.read_csv(folder   sys.argv[1], names=colnames)
df2 = df["keywords"].values.tolist()
df2.remove("Keywords")

dataset = []

#for x in range(0,len(df2)):
#     keywords = [df2[x]]
#     pytrend.build_payload(
#     kw_list=keywords,
#     cat=0,
#     timeframe='today 12-m',
#     geo='')
#     data = pytrend.interest_over_time()
#     if not data.empty:
#          data = data.drop(labels=['isPartial'],axis='columns')
#          dataset.append(data)

dataset = [df] # test
result = pd.concat(dataset, axis=1)
result.to_csv(folder   sys.argv[2])

executionTime = (time.time() - startTime)
print('Execution time = {:.2f} secs'.format(executionTime))