You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
OS (Windows 11 Pro Operating system)
xlwings version: 0.30.13, Excel and Python (Office 365 for Enterprise, Python 3.12.1)
Describe your issue (incl. Traceback!)
I have frozen the python script using "pyinstaller", and the purpose of this is to make each and every colleague has a way to run python code on their own device. Then, I referenced the ".exe" file in my "xlsm" workbbok using "RunFrozenPython" in VBA, the entire folder is in Sharepoint, and the issue is: whenever I run it on my computer, the dashboard get immediate update, but when my colleague clicks the "refresh" button I created using Macro, an error message always pops up as follow. Am I missing any step? do I need to configure the computer of all my colleagues? thank you very much.
#### Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)
```python
`import pandas as pd
import xlwings as xw
import matplotlib.pyplot as plt
import numpy as np
def main():
pp_old_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\Price Report - PP-OLD.xlsx"
pp_new_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\Price Report - PP-NEW.xlsx"
sp_new_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\Price Report - SP-NEW.xlsx"
sp_old_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\Price Report - SP-OLD.xlsx"
budget_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\budget.xlsx"
db_path = r"C:\Users\rren\MES\Accounting Department - Documents\Ryan Ren - Financial & SCM Analysis_Ryan\55. Price Adjustment Analysis - Last price\Price Adjustment Analysis.xlsm"
budget = pd.read_excel(budget_path)
budget["Customer Name"].replace("Jabil", "JABIL", inplace=True)
pp_new = pd.read_excel(pp_new_path, index_col=0)
pp_old = pd.read_excel(pp_old_path, index_col=0)
sp_new = pd.read_excel(sp_new_path, index_col=0)
sp_old = pd.read_excel(sp_old_path,index_col=0)
budget_customer = budget[["Item No.", "Customer Name"]].drop_duplicates()
budget1 = pd.DataFrame(budget.groupby(["Item No."])["Final Annual Qty"].mean())
budget1.reset_index(inplace=True)
budget2 = budget1.merge(budget_customer, on="Item No.", how= "left")
# Convert price string to price numeric and dynmaic change the name of the header for sp_new
sp_new = sp_new[["Item No.", "Primary Currency - Price", "Source of Price"]]
sp_new.loc[:,"Primary Currency - Price"] = sp_new["Primary Currency - Price"].str.replace("$"," ")
sp_new.loc[:, "Primary Currency - Price"] = sp_new["Primary Currency - Price"].str.replace("EURO", " ")
sp_new.loc[:,'Primary Currency - Price'] = sp_new['Primary Currency - Price'].str.replace(',', '')
sp_new["Primary Currency - Price"] = pd.to_numeric(sp_new["Primary Currency - Price"])
mapper1 = {"Primary Currency - Price": sp_new["Source of Price"][1]}
sp_new.rename(columns=mapper1, inplace=True)
sp_new = sp_new.drop(columns=["Source of Price"])
# Convert price string to price numeric and dynmaic change the name of the header for sp_old
sp_old = sp_old[["Item No.", "Primary Currency - Price","Source of Price"]]
sp_old.loc[:,"Primary Currency - Price"] = sp_old["Primary Currency - Price"].str.replace("$"," ")
sp_old.loc[:,'Primary Currency - Price'] = sp_old['Primary Currency - Price'].str.replace(',', '')
sp_old["Primary Currency - Price"] = pd.to_numeric(sp_old["Primary Currency - Price"])
mapper2 = {"Primary Currency - Price": sp_old["Source of Price"][1]}
sp_old.rename(columns=mapper2,inplace=True)
sp_old = sp_old.drop(columns=["Source of Price"])
# Convert price string to price numeric and dynmaic change the name of the header for pp_new
pp_new = pp_new[["Item No.", "Primary Currency - Price","Source of Price"]]
pp_new.loc[:,"Primary Currency - Price"] = pp_new["Primary Currency - Price"].str.replace("$"," ")
pp_new.loc[:, "Primary Currency - Price"] = pp_new["Primary Currency - Price"].str.replace("USD", " ")
pp_new.loc[:,'Primary Currency - Price'] = pp_new['Primary Currency - Price'].str.replace(',', '')
pp_new["Primary Currency - Price"] = pd.to_numeric(pp_new["Primary Currency - Price"])
mapper3 = {"Primary Currency - Price": pp_new["Source of Price"][1]}
pp_new.rename(columns=mapper3,inplace=True)
pp_new = pp_new.drop(columns=["Source of Price"])
# Convert price string to price numeric and dynmaic change the name of the header for pp_old
pp_old = pp_old[["Item No.", "Primary Currency - Price","Source of Price"]]
pp_old.loc[:,"Primary Currency - Price"] = pp_old["Primary Currency - Price"].str.replace("$"," ")
pp_old.loc[:,'Primary Currency - Price'] = pp_old['Primary Currency - Price'].str.replace(',', '')
pp_old["Primary Currency - Price"] = pd.to_numeric(pp_old["Primary Currency - Price"])
mapper4 = {"Primary Currency - Price": pp_old["Source of Price"][1]}
pp_old.rename(columns=mapper4,inplace=True)
pp_old = pp_old.drop(columns=["Source of Price"])
budget3 = budget2.merge(pp_old, on="Item No.", how="left").merge(pp_new, on="Item No.", how="left").merge(sp_old,on="Item No.", how="left").merge(sp_new, on="Item No.", how="left")
pp_header1 = budget3.columns[3]
pp_header2 = budget3.columns[4]
pp_header3 = budget3.columns[5]
pp_header4 = budget3.columns[6]
budget3["Var. PP"] = budget3[pp_header1] - budget3[pp_header2]
budget3["Var. SP"] = budget3[pp_header4] - budget3[pp_header3]
budget3["Savings for MES - PP"] = budget3["Final Annual Qty"] * budget3["Var. PP"]
budget3["Savings for MES - SP"] = budget3["Final Annual Qty"] * budget3["Var. SP"]
budget3["Sub Total"] = budget3["Savings for MES - PP"] + budget3["Savings for MES - SP"]
budget4 = budget3.groupby(["Customer Name","Item No."])[["Savings for MES - PP", "Savings for MES - SP", "Sub Total"]].sum().sort_values("Savings for MES - SP")
budget5 = budget4[(budget4["Savings for MES - PP"]!=0) | (budget4["Savings for MES - SP"]!=0) ]
budget6 = budget5.reset_index()
wb = xw.Book(db_path)
sheet1 = wb.sheets("Detailed Analysis")
cust_filter = sheet1.range("C5").value
budget7 = budget6[budget6["Customer Name"] == cust_filter]
budget8 = budget7[(budget7["Savings for MES - PP"] != 0) | (budget7["Savings for MES - SP"]!= 0)]
budget9 = budget8.drop(columns=["Customer Name"]).sort_values("Sub Total")
sheet1.range("A8:E200").clear_contents()
sheet1.range("F8:J200").clear_contents()
top10_pp = budget9["Savings for MES - PP"].sort_values().head(10)
top10_sp = budget9["Savings for MES - SP"].sort_values().head(10)
top10_SubTtoal = budget9["Savings for MES - PP"].sort_values().head(10)
if budget9["Item No."].count() == 0:
sheet1.range("c8").value = "There is no price adjustment for the selected cusomter"
sheet1.range("h8").value = "There is no price adjustment for the selected cusomter"
for shape in sheet1.shapes:
if shape.left == sheet1.range("L1").left and shape.top == sheet1.range("L1").top:
shape.delete()
elif budget9["Item No."].count() <= 20:
sheet1.range("A8").value = budget9
sheet1.range("H8").value = "All records have been displayed in the chart on left"
fig = plt.figure(figsize=(20,8))
plt.suptitle(f"Detailed analysis for {cust_filter}")
plt.style.use("seaborn-v0_8")
plt.subplot(3,1,1)
plt.title("Savings for MES - PP")
plt.bar(budget9["Item No."][:10], top10_pp, color="#4682B4")
plt.subplot(3,1,2)
plt.title("Savings for MES - PP")
plt.bar(budget9["Item No."][:10], top10_sp, color="#F4A460")
plt.subplot(3,1,3)
plt.title("Savings for MES - PP")
plt.bar(budget9["Item No."][:10], top10_SubTtoal, color="#ffb3b3")
plt.tight_layout()
# delete existing shape
for shape in sheet1.shapes:
if shape.left == sheet1.range("L2").left and shape.top == sheet1.range("L2").top:
shape.delete()
# write new shape
plot = sheet1.pictures.add(fig)
plot.left = sheet1.range("L1").left
plot.top = sheet1.range("L1").top
else:
sheet1.range("A8").value = budget9.head(20)
sheet1.range("F8").value = budget9.tail(20)
fig = plt.figure(figsize=(20,10))
plt.suptitle(f"Detailed analysis for {cust_filter}")
plt.style.use("fivethirtyeight")
plt.subplot(3,1,1)
plt.title("Savings for MES - PP", pad=2)
plt.bar(budget9["Item No."][:10], top10_pp, color="#4682B4")
plt.subplot(3,1,2)
plt.title("Savings for MES - PP",pad=2)
plt.bar(budget9["Item No."][:10], top10_sp, color="#F4A460")
plt.subplot(3,1,3)
plt.title("Savings for MES - PP", pad=0.01)
plt.bar(budget9["Item No."][:10], top10_SubTtoal, color="#ffb3b3")
plt.tight_layout()
for shape in sheet1.shapes:
if shape.left == sheet1.range("L1").left and shape.top == sheet1.range("L1").top:
shape.delete()
plot = sheet1.pictures.add(fig)
plot.left = sheet1.range("M1").left
plot.top = sheet1.range("M1").top
plt.subplots_adjust(top=0.85)
# Write the entire data into the workbook
sheet1.range("A35:L400").clear_contents()
sheet1.range("A35").value = budget3[(budget3["Customer Name"]==cust_filter) & ((budget3["Savings for MES - PP"] != 0) | (budget3["Savings for MES - SP"] != 0))].drop(columns=["Customer Name"])
if __name__ == '__main__':
main()`
```VBA
![image](https://github.com/xlwings/xlwings/assets/141468315/727d6ea2-70ff-4240-9b08-d5f28f77bc9e)
```Error message returned from co-worker's computer:
![image](https://github.com/xlwings/xlwings/assets/141468315/cce60e43-b08c-40d0-9b9d-719c812acc38)
The text was updated successfully, but these errors were encountered:
OS (Windows 11 Pro Operating system)
xlwings version: 0.30.13, Excel and Python (Office 365 for Enterprise, Python 3.12.1)
Describe your issue (incl. Traceback!)
I have frozen the python script using "pyinstaller", and the purpose of this is to make each and every colleague has a way to run python code on their own device. Then, I referenced the ".exe" file in my "xlsm" workbbok using "RunFrozenPython" in VBA, the entire folder is in Sharepoint, and the issue is: whenever I run it on my computer, the dashboard get immediate update, but when my colleague clicks the "refresh" button I created using Macro, an error message always pops up as follow. Am I missing any step? do I need to configure the computer of all my colleagues? thank you very much.
The text was updated successfully, but these errors were encountered: