CODEWITHSUNDEEP
excelvba
DGMS89
DGMS89

Reputation: 1677

Running Python script from vba with network paths

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?

Upvotes: 2

Views: 810

Answers (1)

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 3

Related Questions