0

I have a problem with my VBA code to call a Python Script, which downloads a CSV from a web page. In Visual Studio Code, I can execute it and the date of my result file changes. But, if I try to call this script in VBA, open python.exe, do something (I don't know), and close it, and my date of my csv file doesn't upload.

My VBA code is:

Sub Download_CSV_Python()

'Procedure to run a Python Script in a VBA Procedure using the shell
 
    'Declaration
    Dim objShell As Object 'For the Shell
    Dim Pythonexe, PythonScript As String
     
    'Create the Shell Object
    Set objShell = VBA.CreateObject("WScript.Shell")
    
    'path of the python.exe
    Pythonexe = """C:\Users\sergi\AppData\Local\Programs\Python\Python310\python.exe"""
    
    'path of your Python script
    PythonScript = "C:\Users\sergi\Desktop\Python\CSVChrome4.py"
     
    'Run your Python script
    objShell.Run Pythonexe & PythonScript
 
    'free variables for memory
     Set objShell = Nothing
      
End Sub

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("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))
enter code here
Sergio
  • 1
  • 1
  • This is confusing, but just to be clear, [tag:VB.Net] (from [tag:visual-studio]) and [tag:VBA] (from [tag:ms-office]) are similar but not the same thing, and [tag:Python] is a completely different language. – ashleedawg Jan 28 '22 at 02:03
  • You need a space between the program and the parameter. What you've done is basically `pythonCSVChrome4.py`. A debug print would have shown you this. – Tim Roberts Jan 28 '22 at 02:07
  • Maybe try a slightly different approach, so you can check any output from your script: eg something like https://stackoverflow.com/a/70268284/478884 – Tim Williams Jan 28 '22 at 02:16
  • ...or something like https://stackoverflow.com/a/69367057/478884 so you can grab the script output in your VBA. – Tim Williams Jan 28 '22 at 02:23
  • mmm ok. I try this code, shell is opened and ejecute "correcly" the .py. But i have a problem. When i go to explorer files after exetute by Shell, dont appear new date. However, if i execute the .py in Visual Studio Code, execute correctly and date of file "search_trends.csv" is updated. ¿Why executing by Shell dont update the file? I put my .py code in principal message. – Sergio Jan 28 '22 at 18:49

0 Answers0