ChatGPT: Automating Excel with VBA like never before 🤯
Introduction
In this post, I will explore the full potential of automating Excel tasks using VBA and ChatGPT. I will show you how to easily extract data from multiple workbooks, send emails with personalized text and attachments, replace values in spreadsheets, and even automate the creation of pivot tables and charts.
1. Example: Extract data from multiple files
Let’s start by consolidating data from multiple Excel files. In my input folder, I have a collection of files that all have the same structure – they contain a “Settings” and “Data” worksheet. The “Settings” sheet holds the information I need, specifically the country, entity, and name from each file.
To do this, I asked ChatGPT to create a VBA script for the ‘consolidated.xlsm’ workbook to extract the values in Cell A2, B2, and C2 from the “Settings” sheet from all Excel files in the ‘input’ folder located in the same directory as my workbook. The extracted values should be inserted into my workbook in columns A, B, C, and D, along with the source file name.
After receiving the code, I pasted it into my workbook, compiled it, and ran it. Excel opened each file and wrote the values into my workbook. The results showed the settings extracted from each file along with the corresponding workbook name. This basic code can be tweaked for speed and error handling, but it serves as a great starting point.
2. Example: Send personalized emails
Next, I created an email sender tool in Excel. I have an attachment folder with files to send via email, and a workbook containing a table of email receivers, CC email addresses, recipient names, attachment file names, a subject, and a body. I converted this range into an Excel table named “tbl_EMAIL_LIST.”
Using ChatGPT, I generated a VBA macro that loops through the rows of the “EMAIL_LIST” table in the “distribution_list” worksheet. The macro uses the “Email Receiver” column for the recipient, “Email CC” for CC, and replaces placeholders in the email body with actual values from the table.
After pasting the code and fixing some minor errors related to column references and Outlook library imports, I executed the code. It successfully created draft emails with the correct recipients, subjects, attachments, and body text. ChatGPT made the coding process much easier, although some VBA knowledge was still necessary for modifications.
3. Example: Find and replace values
In another example, I wanted to replace “Small Business” with “Small Market” and “Midmarket” with “Midsize Market” in multiple Excel files. The modified worksheets should be saved in an Output folder, which VBA should create if it doesn’t exist.
After asking ChatGPT, I received VBA code that handled this task. However, I encountered a compile error due to undeclared string variables for the input and output folder paths. After declaring these paths and fixing them in the code, the script ran successfully, creating a new output folder with the modified files.
4. Example: Create pivot tables and charts
For the final example, I needed to automate the creation of a pivot table and a bar chart to display aggregated sales by product from financial data in a worksheet. I asked ChatGPT to group the data by product and create a bar chart in a new worksheet named ‘Analysis.’
After inserting the provided code and resolving an error related to the grouping property, I executed it. The pivot table was created, but some columns were missing. I adjusted the data range to include all relevant columns and reran the code, resulting in a complete pivot table and chart.
Outro
These examples demonstrate how, with a bit of VBA knowledge, you can leverage ChatGPT to significantly speed up your workflows in Excel. The results and code generated by ChatGPT are promising and will likely improve in the future. What do you think about using ChatGPT for automating Excel tasks? Let me know your thoughts!
