Hey guys,
In this video, we will explore the full potential of automating Excel tasks using VBA and ChatGPT. In particular, 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 how to automate the creation of pivot tables and
charts. Ok, and without further ado, let us dive in. Let's start by exploring how to consolidate
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 "Data" worksheet includes a collection
of numbers, but I'm more interested in the "Settings" sheet. Specifically, I want to extract 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 sheet "Settings" from all Excel files in the 'input' folder located in the same
directory as my workbook. And it should insert the extracted values,
along with the source file name, into my workbook in columns A, B, C, and D." After hitting enter, I received a VBA code
and some explanations in return. All I had to do was copy this code into my
consolidated workbook. So, I opened the workbook, went to Developer,
Visual Basic, inserted a new module, and pasted the code there. Before running any macro, I typically compile
it first by clicking on the button under the debug menu. The code compiled without any errors, so I
ran the code to see the results. Excel is now opening each file and is writing
the values in my workbook. Once that was done, I received a message box. And this is what the result looks like. We have extracted the settings from each file,
and in column D, we can also see the corresponding workbook name. Of course, you could tweak the code further,
such as setting the Application.ScreenUpdating to False to speed up the macro or include
additional error handling. But I think the code from chatGPT is already
a great starting point, and we achieved this with minimal effort. And with that said, let's move on to creating
an email sender tool in Excel. To do this, I have an attachment folder where
I keep all the files I want to send via email. To send these files, I have created the following
spreadsheet. This workbook contains a table of email receivers,
cc email addresses, recipient names, attachment file names, a subject, and a body. Additionally, I have done some further preparation. I converted this range into an Excel table
and named it "tbl_EMAIL_LIST." For the subject, I used a named cell called
"SUBJECT," and I did the same for the body as well. This is not necessary, but I like to do it
this way because it allows me to move the cells around, but the name of the cell will
remain the same. Additionally, you will notice that I have
included placeholders in the email body. So, {NAME} should be replaced with the actual
name in column C, and {ATTACHMENT} should be replaced with the attachment name in column
D. To generate the VBA code for this task, I
asked ChatGPT to "Create a VBA macro that loops through the rows of the "EMAIL_LIST"
table in the "distribution_list" worksheet. For each row, use the "Email Receiver" column
for the recipient, "Email CC" for the CC, and the "Name" column to replace the {NAME}
placeholder in the "BODY" cell. Also, it should replace the {ATTACHMENT} placeholder
with the corresponding value in the "Attachment" column. Use the "SUBJECT" cell for the subject of
the email. Also, attach the file found in the "attachments"
folder of the workbook's directory, using the "Attachment" column to concatenate the
file path. Send the email using Outlook." Although I received a warning that this prompt
may violate the content policy, I still received the following VBA code. The formatting is slightly off, but that should
not be an issue. More importantly, the solution included a
hint. The provided code assumes that the email receiver
is in the second column, followed by the other fields. However, in my case, the email address is
located in column A. So, I need to fix this later. I copied the code, returned to my workbook,
inserted a new module, pasted the code, and compiled it. However, I received a compile error saying
that the "user-defined type is not defined." I could have asked chatGPT to fix this error,
but I already knew the solution. To interact with Outlook, I first needed to
import the library by going to tools, references, searching for the "Microsoft Outlook object
library," and ticking the box next to it. Once that was done, I also fixed the column
numbers. In my workbook, the recipient starts from
column A, but the code assumes it is in column B. So, I replaced 2 with 1. And I did the same for the other columns. Also, the loop should start from row 2, as
in the first row, we have the headers. And for testing purposes, I only want to display
the emails and not send them. After making these modifications, I executed
the code, and it created three emails for me. I dragged them down a bit to check if everything
was correct. And it looked good to me. The receiver, CC, subject, attachment, and
body were all correct. We can also see that the placeholders for
the name and attachments have been replaced. From this example, we can see that ChatGPT
did all the heavy lifting to create the code. However, if you want to modify it, it's good
to have some knowledge of VBA. Nonetheless, you don't need to be an expert
to create a tool like this. With that in mind, let's take a look at the
next example. The use case is as follows: I have an input
folder with a bunch of Excel files. Let me open one. Within all files and worksheets, I want to
replace "Small Business" with "Small Market" and "Midmarket" with "Midsize Market". The modified worksheets should be saved in
an Output folder. If that folder does not exist, VBA should
create it. I asked ChatGPT if that would be possible. In response, it answered that it is possible
and laid out how I could tackle this problem. I then asked for the complete VBA code, and
ChatGPT provided the following script. It is important to note the additional comments
stating that we need to modify the input and output folder path. With that in mind, I grabbed this code, opened
my workbook, inserted a new module, and pasted it in. I already like that it even created a separate
function to check whether a given folder exists. However, when I compiled the code, I received
an error stating that the "Input Folder Path" was not defined, which was correct. The variables had not been declared as strings. I went back to the chat and pasted the error
message. ChatGPT recognized the issue and said that
those two variables should be defined before being used. However, in the modified version, they had
still not been declared. So, I decided to do it myself. I declared "inputfolderpath" and "outputfolderpath"
as strings and also fixed the input and output folder paths. For my example, both directories should be
in the same directory as the current workbook. I recompiled the code, and this time, there
was no error, so I decided to give it a try and run the code. Once it was done, I received a message box. When I minimized Excel, I could see that a
new output folder had been created. Within this folder, I found my workbook. To validate that the changes had been made,
I opened one file and saw that "Small Business" had been replaced with "Small Market" and
"Midmarket" with "Midsize Market". I think the results are pretty impressive,
and this can be a real time-saver. Ok, for the last example, let's see if we
can speed up our analysis in the following workbook. We have some financial data for different
countries, and I'd like to automatically insert a pivot table and create a bar chart to display
the aggregated sales by product. I asked ChatGPT: "Using VBA, can you please
group the data in the worksheet "Data" in column J, so the Sales by column C, the products? Additionally, can you create an excel bar
chart from the grouped product sales and place it in a new worksheet called 'Analysis'?". And those two sentences were already enough
to receive the following VBA code. I copied the code, inserted a new module,
and pasted it in. The code compiled without any errors, so I
gave it a shot and executed it. However, I received an error saying that VBA
could not get the group property from the range class. I took this error message and pasted it into
the chat. I then received a new version of the code. I took this modified version and replaced
the code. Yet, this time, I received another error message
when creating the pivot table. However, I noticed that in my workbook, the
pivot table had already been created. So, I decided to comment out those lines to
see if it could also generate the pivot chart. And indeed, after commenting out the lines
and running the rest of the code, we now had a bar chart with the product sales. But, looking at the available fields, I noticed
that some columns were missing. So, I deleted the sheet and went back to the
VBA code. I set the data range from column A to P to
include all data. I also removed the lines I had commented out. Now, if we rerun it, we have our pivot table
and chart as before, but this time, we also have all the columns available so that I can
filter the data further. I think these examples are pretty amazing. With just a little knowledge of VBA, you can
take full advantage of ChatGPT and speed up all workflows. And I imagine the results and code from ChatGPT
will only improve in the future. So, what's your opinion about ChatGPT? Let me know in the comments. And as always, thanks for watching and see
you in the next video.
