Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Compile error "Can not find Project and Library" , Runpython works well on my device but error returned on co-worker's devices #2403

Open
Dulaclyon opened this issue Feb 22, 2024 · 0 comments

Comments

@Dulaclyon
Copy link

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.

image


#### 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)




Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant