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

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.

the cmd opened now, but how can I reference the specific python script?
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 - 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.
@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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Linked

Related