I was tasked with building a multi-step program that took a large Excel file's tabs, which were updated based on a unique cell value, and then import some of those tabs into a unique Powerpoint, which is then saved into a folder and that folder than grows larger as the program loops through multiple value, which continues to update the Excel file.
I originally used vba, but found that vba in Excel does not allow me to update links that sit in Powerpoint, only Excel links would work.
In a nutshell the code below takes a value from my looper file, inputs it into Excel, which then updates the Powerpoint slides that are linked to those tabs. My solution works, but when I open the files, I must click don't update links or else it reverts to whatever tabs are showing on the Excel file.
My problem is that I don't know how to break the links to Excel file in the Powerpoint before it gets saved. I have tried several functions and they all don't seem to work.
Below is my code:
def fun_times():
# Set the path to the existing PowerPoint file
existing_ppt_path = r"C:/Users/dtalbot2/Desktop/x.pptm"
# Create PowerPoint application
ppt_app = win32com.client.Dispatch("PowerPoint.Application")
ppt_app.Visible = True # Uncomment this line if you want PowerPoint to be visible
# Open the existing PowerPoint presentation
ppt_pres = ppt_app.Presentations.Open(existing_ppt_path)
# Get the Excel application
excel_app = win32com.client.Dispatch("Excel.Application")
# Set paths and filenames
looper_path = r"C:/Users/dtalbot2/Desktop/Looper1.xlsx"
b_p_path = r"C:/Users/dtalbot2/Desktop/z.xlsm"
for i in range(1, 181):
# Activate Looper workbook
base_wb = excel_app.Workbooks.Open(looper_path)
base_wb.Activate()
# Set value in Looper worksheet
base_wb.Worksheets("Looper").Range("E1").Value = i
retailer_name = base_wb.Worksheets("Looper").Range("F1").Value
retailer_code = base_wb.Worksheets("Looper").Range("G1").Value
this_file_name = r"C:\Users\dtalbot2\Documents\BP\\" + str(retailer_name)
# Activate B_P workbook
target_wb = excel_app.Workbooks.Open(b_p_path)
target_wb.Activate()
# Set value in B_P worksheet
target_wb.Worksheets("BP Cover").Range("AF8").Value = retailer_code
ppt_pres.UpdateLinks()
# Save the PowerPoint presentation with the specified filename
ppt_pres.SaveAs(this_file_name + ".pptx") # You can change the extension if needed
# Close workbooks
base_wb.Close(False)
target_wb.Close(False)
# Close PowerPoint presentation
ppt_pres.Close()
# Close PowerPoint application
ppt_app.Quit()
if __name__ == "__main__":
fun_times()
I tried defining a break link function above the fun_times() functions and then including it in the fun times function and that didn't work. Instead the program basically gets stuck at break link function. It doesn't work.