VBA - call Python script

Question

I have read several threads and found that I can call Python from VBA using Shell.

This is what I have:

Private Sub CommandButton1_Click()

    Ret_Val = Shell("C:\Users\....\Anaconda3\python.exe C:\Users\...\REL.py", vbNormalFocus)

    If Ret_Val <> 0 Then
       MsgBox "ok!", vbOKOnly
    End If

End Sub

I do get the OK message, and a window open and close within a second. So my guess it is launching Python but the script does not work.

So I thought that maybe does not work because I wrote the code in spyder and I might need to launch spyder:

Ret_Val = Shell("C:\Users\...\Anaconda3\Scripts\spyder.exe C:\Users\...\REL.py", vbNormalFocus)

This will open Spyder, which takes too much time and it is not what I want. Also once it is opened, it does not launch the script.

Something is wrong, but I am not sure if it is due to the VBA code or something else related to the script. The script works when I run it from Spyder without VBA.

This is a simple example of the Python code:

# -*- coding: utf-8 -*-
"""
Created on Fri Apr 21 08:24:05 2017

@author: xxxxxx
"""
import pandas as pd
import numpy as np
import datetime

RW = pd.read_excel('Raw_data.xlsx', skipinitialspace=True) 
RW=pd.DataFrame(RW, columns = ['Task Id', 'TIDmodRW', 'Start Date', 'End Date', 'Machine', 'Boards'])

writerRW = pd.ExcelWriter('RW_test2.xlsx', engine='xlsxwriter')
RW.to_excel(writerRW, sheet_name='sheet1')
writerRW.save()

Why don't you give a sample Python script to make this a minimal reproducible example? — John Coleman
– John Coleman, Commented May 1, 2017 at 23:27
I added it in the question. I cut it to keep it simple, but it does not write the excel. The code works with Spyder — Filippo
– Filippo, Commented May 1, 2017 at 23:45
Perhaps you can add some error handling to the Python script, where the error handler prints the exception and has an input("Press any key to close") so that you can view the result before the window closes. By judicious use of print(), can you identify the line in the Python code which is throwing the error? — John Coleman
– John Coleman, Commented May 1, 2017 at 23:54
When using shell to pass file paths as arguments it's usually good practise to put the path(s) in quotes, in case they contain spaces.., - Shell("C:\Users\....\Anaconda3\python.exe ""C:\Users\...\REL.py"" ", vbNormalFocus) Double-up the quotes to escape them as shown. — Tim Williams
– Tim Williams, Commented May 2, 2017 at 0:03
...also make sure that within your python script the paths you're using are sufficient - not sure what python defaults to when only given a filename and not a full path: if it's the "current directory" then that might be different running from VBA vs. running directly. stackoverflow.com/questions/11073553/… — Tim Williams
– Tim Williams, Commented May 2, 2017 at 0:06

Parfait · Accepted Answer · 2017-05-02 11:23:51Z

Consider using the built-in os module's path.dirname() to automatically detect the current path of your python script to locate the input Excel file (assuming the workbook is in same directory as .py script). Then, use the OS-agnostic path.join() to concatenate every file string with it for full directory reference. This method does not require backslashes (i.e., Windows) or forward slashes (i.e., Linux/Mac), so should work across operating systems.

So now, whatever folder the Excel file and Python file reside or whatever the call method including Spyder, VBA, or command line, the script should run fully reading and writing to local workbooks.

import os                                                # ADD IMPORT
import pandas as pd
import numpy as np
import datetime

cd = os.path.dirname(os.path.abspath(__file__))          # LOCATE CURRENT DIR

# CONCATENATE CURRENT DIRECTORY TO EXCEL BASE FILE NAME
RW = pd.read_excel(os.path.join(cd, 'Raw_data.xlsx'), skipinitialspace=True) 
RW = pd.DataFrame(RW, columns = ['Task Id', 'TIDmodRW', 'Start Date', 
                                 'End Date', 'Machine', 'Boards'])

writerRW = pd.ExcelWriter(os.path.join(cd, 'RW_test2.xlsx'), engine='xlsxwriter')
RW.to_excel(writerRW, sheet_name='sheet1')
writerRW.save()

Collectives™ on Stack Overflow

VBA - call Python script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related