0

Im trying to Run the python code from the VBA (Run_report.xlsm).

My xlsm file and all the py files are in the same directory.

Python code has to work on the data which is itself in the Run_report.xlsm file(again this is the file from where i run VBA code to call the python script).

this is the VBA code:

Option Explicit
Sub RunPythonScript()

'Declare Variables
Dim objShell As Object
Dim PythonExe, PythonScript, cmd As String

'Create a new Object shell.
Set objShell = VBA.CreateObject("Wscript.Shell")

'Provide file path to Python.exe
'USE TRIPLE QUOTES WHEN FILE PATH CONTAINS SPACES.
PythonExe = """C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe"""
PythonScript = """C:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py"""

cmd = PythonExe & PythonScript
Debug.Print cmd

'Run the Python Script
objShell.run cmd, 0, True




MsgBox "Finished"

End Sub

and this is the start of the python code to see how I defined the path and reading the files.

import pandas as pd
import numpy as np
import os as os



# Get the user
username = os.getlogin()


# search for directory
directory = r'C:/Users/' + username + '/Digital Wholesale - Documents/2. Amazon/Customer Operations_OTC/02. Amazon weekly Report/Amazon weekly automation/'


# reading SAP extract  files from Run_report.xlsm and creating csv from it

df_1 = pd.read_excel(os.path.join(directory,'Run_report.xlsm'), sheet_name= "weekly",header= None)
df_1 = df_1.drop(df_1.columns[[0,1]], axis=1)
df_1.columns = df_1.loc[3].rename(None)
df_1 = df_1.drop(range(5))

df_1.to_csv(directory + '1.csv', index=False, header= True)


#Read CSV of SAP extract

df_weekly=pd.read_csv(os.path.join(directory,'1.csv'), low_memory=False)

VBA code give me very fast msgbox, and python code is not running.

I checked in CMD and it gave me a PermissionError - because the file is Run_report.xlsm is not closed but I have seen that its possible to Call the python script and do some work in the workbook with pandas even its open. Not sure what Im doing wrong here.

Greencolor
  • 501
  • 1
  • 5
  • 16
  • Before you start, open task manager and kill off all excel and python instances. Are you running a VBA script to run a python program to access the same spreadsheet that contains the VBA script or a different spreadsheet? An open spreadsheet cannot be opened again unless it has been closed or some special type of file sharing is invoked. – cup Feb 09 '22 at 19:35
  • @cup yes, exactly. I am running a VBA script (from Run_report.xlsm file) to run a python (VL.py file) program to access the same spreadsheet(Run_report.xlsm second and third sheets) that contains VBA script – Greencolor Feb 09 '22 at 19:39
  • when I run code from pycharm it works, I get desired out put – Greencolor Feb 09 '22 at 19:42
  • OT: Can't you install xlwings? You could control many parameters that get parsed to your script from excel instance easier – Sgdva Feb 09 '22 at 20:01
  • Looks like you are appending two strings in the cmd variable, without a space between them. so they are viewed as a single command, not a command and a parameter. just change: PythonExe = """C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe""" to PythonExe = """C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe """ 'add a space after python.exe – peterb Feb 09 '22 at 20:02
  • yes, for future I can consider it too but I really want to make this work. I watch youtube videos and they work well. I dont get why I cant make it work – Greencolor Feb 09 '22 at 20:03
  • @peterb Could you please explain with the answer? I literally followed the youtube video step by step. It works for him. – Greencolor Feb 09 '22 at 20:04

1 Answers1

0

This line 'cmd = PythonExe & PythonScript' produces this string: C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exeC:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py

what you want is: C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe C:\Users\gobro7\Digital Wholesale - Documents\2. Amazon\Customer Operations_OTC\02. Amazon weekly Report\Amazon weekly automation\VL.py

so you need a space between the python command and the location of the python script you are running.

just add a space at the end of the first string:

"""C:\Users\gobro7\AppData\Local\Programs\Python\Python39\python.exe """
peterb
  • 697
  • 3
  • 11
  • Yes, I had that version too and did not work. I still get the error `PermissionError: [Errno 13] Permission denied:` – Greencolor Feb 09 '22 at 20:11