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
– Vityata, Commented Jul 11, 2018 at 8:36

Vityata · Accepted Answer · 2018-07-11 11:51:03Z

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, 2018 at 11:51

answered Jul 11, 2018 at 8:45

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

DGMS89 Over a year ago

the cmd opened now, but how can I reference the specific python script?

Vityata Over a year ago

@DGMS89 - by writing its path.

DGMS89 Over a year ago

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?

Vityata Over a year ago

@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 Over a year ago

@DGMS89 - make the Python code really simple. E.g. simply write print 'Something' to see whether it gets printed.

|

Collectives™ on Stack Overflow

Running Python script from vba with network paths

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related