Excel Automation Made Easy with Python and ChatGPT (don’t miss out…)

Introduction

In this tutorial, we will explore how to utilize ChatGPT alongside Python to automate tasks in Excel. This guide will provide you with practical examples of Excel automation, including finding and replacing values, storing values in new workbooks, filtering data, splitting worksheets into separate workbooks, and even automating PowerPoint presentations. Each section will include step-by-step instructions and code snippets to help you implement these solutions effectively.

Chapter 1: Find and Replace Values

Objective

Replace specific values in multiple Excel files located in an input folder and save the modified files in an output folder.

Steps

1. Set Up Your Directory:

   • Create an input folder containing your Excel files.
   • Create an output folder for the modified files.

2. Use ChatGPT to Generate Code:

   • Ask ChatGPT to create a script that will:
     • Iterate through all Excel files in the input directory.
     • Replace "Small Business" with "Small Market" and "Midmarket" with "Midsize Market" in every worksheet.

3. Copy the Generated Code:

   import os
   import pandas as pd
   
   input_folder = 'input'
   output_folder = 'output'
   
   if not os.path.exists(output_folder):
       os.makedirs(output_folder)
   
   for filename in os.listdir(input_folder):
       if filename.endswith('.xlsx'):
           df = pd.read_excel(os.path.join(input_folder, filename), sheet_name=None)
           for sheet, data in df.items():
               data.replace({'Small Business': 'Small Market', 'Midmarket': 'Midsize Market'}, inplace=True)
               data.to_excel(os.path.join(output_folder, f"{sheet}_{filename}"), index=False)
   

4. Run the Script:

   • Execute the script in your Python environment.

5. Verify Output:

   • Check the output folder to ensure that the values have been replaced correctly.

Tips

• Ensure you have pandas installed. Use pip install pandas if necessary.
• Modify the replacement dictionary for additional values as needed.

Chapter 2: Store Values in a New Workbook

Objective

Save each worksheet in an Excel file as a new workbook containing only values while maintaining the original format.

Steps

1. Request Code from ChatGPT:

   • Specify your needs, including creating an output folder if it doesn't exist.

2. Copy the Generated Code:

   import os
   import pandas as pd
   
   input_file = 'input_file.xlsx'
   output_folder = 'Output'
   
   if not os.path.exists(output_folder):
       os.makedirs(output_folder)
   
   df = pd.read_excel(input_file, sheet_name=None)
   
   for sheet_name, data in df.items():
       data.to_excel(os.path.join(output_folder, f"{sheet_name}.xlsx"), index=False)
   

3. Run the Script:

   • Execute the code in your Python environment.

4. Check for Formatting:

   • If formatting is lost, ask ChatGPT for modifications or use the xlwings library for enhanced formatting options.

Common Pitfalls

• Ensure the input file path is correct.
• If formatting is critical, consider using xlwings instead of pandas.

Chapter 3: Filter Excel Data

Objective

Filter financial data for a specific year and save separate Excel files for each country.

Steps

1. Define Your Task:

   • Specify the file and the exact filtering criteria to ChatGPT.

2. Copy the Generated Code:

   import pandas as pd
   import os
   
   file_path = 'Financial_Data.xlsx'
   output_folder = 'attachments'
   
   if not os.path.exists(output_folder):
       os.makedirs(output_folder)
   
   df = pd.read_excel(file_path, sheet_name='data')
   filtered_data = df[df['Year'] == 2021]
   
   for country in filtered_data['Country'].unique():
       country_data = filtered_data[filtered_data['Country'] == country]
       country_data.to_excel(os.path.join(output_folder, f"{country}.xlsx"), index=False)
   

3. Run the Script:

   • Execute the code to create the filtered workbooks.

4. Verify Output:

   • Open the generated files to confirm they contain the correct data.

Practical Advice

• Always double-check your column names in the DataFrame to prevent key errors.

Chapter 4: Split Worksheets into Workbooks

Objective

Separate each worksheet in a workbook into individual workbooks.

Steps

1. Request Code from ChatGPT:

   • Ask for a solution that uses xlwings for maintaining formatting.

2. Copy the Generated Code:

   import xlwings as xw
   import os
   
   input_file = 'data_file.xlsx'
   output_folder = 'Output'
   
   if not os.path.exists(output_folder):
       os.makedirs(output_folder)
   
   wb = xw.Book(input_file)
   
   for sheet in wb.sheets:
       sheet.api.Copy()
       new_wb = xw.Book.caller()
       new_wb.save(os.path.join(output_folder, f"{sheet.name}.xlsx"))
       new_wb.close()
   

3. Run the Script:

   • Execute the code to create separate workbooks.

4. Check Your Output:

   • Verify that each workbook retains the original formatting.

Tips

• Use ChatGPT to ask for comments in the code for better understanding.

Chapter 5: Automate PowerPoint

Objective

Create a PowerPoint presentation programmatically using Python.

Steps

1. Define Your Requirements:

   • Outline the structure of the presentation to ChatGPT.

2. Copy the Generated Code:

   from pptx import Presentation
   from pptx.util import Inches
   
   def create_slide(prs, title, bullets):
       slide = prs.slides.add_slide(prs.slide_layouts[1])
       slide.shapes.title.text = title
       for bullet in bullets:
           slide.shapes.placeholders[1].text += bullet + '\n'
   
   prs = Presentation()
   create_slide(prs, "Introduction", ["Bullet 1", "Bullet 2", "Bullet 3"])
   create_slide(prs, "Methods", ["Bullet 1", "Bullet 2", "Bullet 3"])
   create_slide(prs, "Results", ["Bullet 1", "Bullet 2", "Bullet 3"])
   
   prs.save('my-awesome-ppt.pptx')
   

3. Run the Script:

   • Execute the code to generate the PowerPoint file.

4. Review the Presentation:

   • Open the PowerPoint file and adjust any formatting manually if necessary.

Practical Advice

• Be specific about layout and design in your prompts to get a closer result to your vision.

Conclusion

In this tutorial, we learned how to automate various tasks in Excel and PowerPoint using Python and ChatGPT. By leveraging ChatGPT's capabilities, you can streamline your workflows and improve productivity. Remember to experiment with different prompts and Python packages to optimize your automation tasks further. Happy coding!