Running Python script from vba with network paths

Question

Scenario: I have a simple VBA code that should run a python script.

Problem: For some reason, this code (which is a direct variation of what I found here: How to call python script on excel vba? ) does not appear to work. It runs the VBA part to completion but does not perform any of the functions of the python script.

VBA code:

Option Explicit

Sub Macro1()
    Dim args As String
    Dim Ret_Val

    args = "\\Network\structured\Uploader_v2.py"
    Ret_Val = Shell("C:\Users\DGMS\AppData\Local\Continuum\anaconda2\python.exe" & " " & args, vbNormalFocus)

End Sub

Python code:

import pandas as pd
import datetime
import os
import Tkinter
from Tkinter import Tk
from tkFileDialog import askdirectory
from Tkinter import filedialog
from os import listdir
from os.path import isfile, join
import glob

# Get input and output paths
Tk().withdraw() 
sourcefolder =  askdirectory()
outputfolder = askdirectory()
selectmonth = raw_input("Please enter month ('January', 'February'...:")

# Get content
all_files = glob.glob(os.path.join(sourcefolder, "*.xls*"))
contentdataframes = []
contentdataframes2 = []

for f in all_files:
    df = pd.read_excel(f)
    df['Name'] = os.path.basename(f).split('.')[0].split('_')[0]
    mask = df.columns.str.contains('Base')
    c2 = df.columns[~mask].tolist()
    df = df[c2]
    contentdataframes.append(df)

concatenatedfinal = pd.concat(contentdataframes)

concatenatedfinal .to_excel(outputfolder + "/" + selectmonth + "_Upload.xlsx",index=False)

Obs: My python code basically gets some path inputs as from the user (filedialog) then gets some data and saves to another file.

Question: Considering that the python code works when run on its own, what may be the reason for it not the be run properly from excel? Is there a way to fix this?

Can you try only `Shell "C:\Users\DGMS\AppData\Local\Continuum\anaconda2\python.exe"` — Vityata, Jul 11 '18 at 08:36

Vityata · Accepted Answer · 2018-07-11T11:51:03.457

3

The way that I am using:

Shell "cmd.exe /S /c " & "C:\somePath\Uploader_v2.py"

This is some code sample, that I have built some time ago:

Option Explicit

Sub TestMe()

    Dim path As String: path = "C:\Python\"
    Dim pathExe As String
    Dim i As Long
    Dim txtStream As TextStream        'Library - Microsoft Scripting Runtime
    Dim fso As New FileSystemObject    'Library - Microsoft Scripting Runtime
    Dim fileName As String

    Columns("C:D").Clear
    For i = 1 To 8

        fileName = "file" & i & ".txt"
        pathExe = path & "CodeForces.py" & " """ & Cells(i, 1) & """ >" & path & fileName
        Shell "cmd.exe /S /c " & pathExe

        Application.Wait Now + #12:00:01 AM#
        Set txtStream = fso.OpenTextFile(path & fileName)
        Cells(i, 3) = txtStream.ReadLine
        txtStream.Close
        'Kill path & fileName

        If Cells(i, 3) = Cells(i, 2) Then Cells(i, 4) = "Pass..."

    Next i

End Sub

It runs a Python exe in C:\Python\CodeForces.py and the result from it is exported to a notepad.

edited Jul 11 '18 at 11:51

answered Jul 11 '18 at 08:45

Vityata

42,633
8
55
100

the cmd opened now, but how can I reference the specific python script? – DGMS89 Jul 11 '18 at 08:47
@DGMS89 - by writing its path. – Vityata Jul 11 '18 at 08:50
Your answer works if the script and the .exe are in the same folder, but I cant get it to work in different folders. I am using: Shell "cmd.exe /S /c " & "C:\Users\DGMS\AppData\Local\Continuum\anaconda2\python.exe" & "\\Network\structured\Uploader_v2.py" but nothng seems to happen. Is there a way to fix this? – DGMS89 Jul 11 '18 at 08:56
@DGMS89 - Can you try `Shell "cmd.exe /S /c " & "\\Network\structured\Uploader_v2.py"` Although, I am not sure how many versions of Python do you have. I usually work with one. – Vityata Jul 11 '18 at 09:01
Nothing seems to happen. I am also trying it with all of the python script commented out, except for "selectmonth = raw_input("Please enter month ('January', 'February'...:")", still nothing happens. – DGMS89 Jul 11 '18 at 09:06
1

@DGMS89 - make the Python code really simple. E.g. simply write `print 'Something'` to see whether it gets printed. – Vityata Jul 11 '18 at 09:09
Same result, I am trying all the simplest code variations I can think of, to no avail. It appears to open the cmd, but close it instantly after. – DGMS89 Jul 11 '18 at 09:13
@DGMS89 - so the problem is that the cmd gets closed? Then write something for input, it thus it would wait - https://stackoverflow.com/questions/70797/python-user-input-and-commandline-arguments – Vityata Jul 11 '18 at 09:15
Just tried that, it appears that the cmd part of your command is working, but it does not read the .py file for some reason. – DGMS89 Jul 11 '18 at 09:21
@DGMS89 - try to put the `.py` file on your PC, not on a server and give it a try. – Vityata Jul 11 '18 at 09:22
Same result. It only worked when I hat both the .exe and the .py in the same folder. – DGMS89 Jul 11 '18 at 09:28
@DGMS89 - You need "Environment variables" and "Add Python exe to path". Read this, it would help you - https://www.howtogeek.com/197947/how-to-install-python-on-windows/ – Vityata Jul 11 '18 at 09:30
I completely reinstalled python and all libraries in my PC. The result is the same, this is indeed very weird. – DGMS89 Jul 11 '18 at 11:10
@DGMS89 - you have to **add Python exe to path**. Read that article. – Vityata Jul 11 '18 at 11:50

Running Python script from vba with network paths

1 Answers1