there's a lot of debate about whether AI tools like chat GPT can truly help us with Excel tasks some love it and others find it to be inaccurate and inefficient so in this video I'll share my perspective and demonstrate how chat gbt can help us automate Excel I'll also share my three-part prompting framework to minimize errors and save time so let's dive in so in this example I have this Excel workbook and it contains these query sheets that each have tables on them and I want to be able to select all of the sheets with
these queries there's 15 of them in total and Export each of the sheets to a CSV file so I can then upload it to a system and I'm going to use chat GPT to help us write a macro that will do all this work for us so we're here in chat gbt now since this is generative AI chat gbt is going to give us a different answer every time we ask it a question and this can be a bit problematic with coding and also with teaching you how to use chat gbt so in order to
teach I'm going to show you prompts that I've already asked it and the answers that I've received so here is my original prompt where I said I would like to get your help creating a VBA Macro for Excel that exports each selected sheet as a CSV file now we can improve this prompt and later in this video I'm going to show you my framework for prompts and a way to improve it but as you can see chat GPT gave us an answer here and if we scroll down we can see the VBA macro that it
has created that we can copy into Excel so go ahead and hit the copy button here we'll jump back over to Excel and we're going to jump into the VB editor and can go to the developer tab and hit the Visual Basic button keyboard shortcut is alt f11 and we're here within this workbook and we can just select the workbook go to insert and choose module keyboard tricket is alt I that will give us a new code module and right here we can just go ahead and paste the code now there are two things I
recommend doing before running the macro the first thing is to read through the code chaty does a great job of commenting the code you can see that with these green lines here these are comments that just explain the block of code below it so read through all of this code and make sure it makes sense to you based on your original prompt and your knowledge of VBA and of course I recommend that you do have knowledge of VBA and I'll talk more about that later in the video the second thing I recommend doing is saving
a copy of your file so here we can just hit these save button and then one easy way to do this is jump to the folder that contains the file I'm just going to uh select it hit crl C and contrl V and that'll make a backup copy and this is always good to do when running macros especially ones that you've copied from the internet because macros can make changes to your file that you won't be able to undo next we can go ahead and test out the macro I'm first going to jump back to
Excel and make sure we have multiple sheets selected so we have these three sheets selected then I'll jump back to the VB editor alt f11 and we can just click anywhere inside the macro here and then hit the Run button keyboard shortcut is F5 and when we run that it's first going to prompt us for the uh folder that we want to export to or save the files to I'll just select that one hit okay and then we get an airor and this can be common with macros that you get from chat GPT so let
me give you a few tips on how to fix this first thing is we get a runtime air 10004 it says you cannot copy or remove group of sheets that contain a table the first thing you can do is hit debug and this is going to highlight the line where the air is raised so this ws. copy line is the line that the air occurs on so I like to give chat GPT that message and I also like to give it the air message now you can type that into chat GPT or one little shortcut
here if you go to the immediate window if you don't see the immediate window keyboard shortcut is contrl G or view tab immediate window down here I'm just going to type a question mark and then e RR for error and then the description can tab into that hit enter and that's going to give us the description that we can now copy and paste into chat GPT so I went back to chat GPT and told that I'm getting a runtime air 10004 on the Ws copy line and then pasted in that airor description and I asked
what might be causing this error forgot the question mark there but that is a question and it came back with a solution which was to convert the tables into a normal range and then it gave the code for that now I definitely do not want to do this if we quickly jump back to the Excel file these are outputs from a power query query and I do not want to convert these to a normal range because then I'd lose the query and it would kind of break my process so of course you can go back
to chat gbt and tell it that or you can just rerun The Prompt and you can do that by hitting this button here and then scroll down I should also mention that I'm using uh GPT 40 which is included on the free plan of chat gbt currently and you can just click this try again button I do have a Plus subscription but I'm using 40 for this video because that's most accessible to everyone and I did make several attempts to try and fix this air in this conversation here I came up with a different solution
which is to copy the sheets contents instead of uh the entire sheet uh that also could potentially work but it ran into an err on this line here with the type name selection I don't believe type name can return uh sheets as a option there so that would need to be fixed also tried to use the search the web fun functionality here or this feature and again still getting an air with that ws copy method I just want to take a quick pause and say that if you're new to our Channel welcome to excel campus
my name is John Ampora and I've been developing Excel and VBA solutions for over 15 years if you're interested in learning more about automating Excel with VBA power query or other tools then check out my free training webinar called the modern Excel blueprint I help reduce the overwhelm by showing you which tools to use and how to save a ton of time with your everyday Excel tasks I'll put a link in the description where you can learn more and join us now one other tip when you run into errors I asked it dis question which
is what else could be causing this air and it came up with a list of seven different things that could be causing the air protected sheets and all of these different options and then at the bottom of this it actually rewrote a macro with all of these different fixes in the macro unfortunately it still didn't fix the air but in some scenarios this can be a great way to understand what could be causing the air if the initial solution doesn't work so I'm back here in the VB editor looking at that original macro and it
turns out the solution here is to just select the sheet first before copying it so here I'm just going to insert a new line I'm going to do ws. select and this will select a single sheet before doing the copy and again this is caused by some weird Behavior with Excel and Excel tables where you can't just copy a sheet when you have sheets with tables selected and I should note that I'm pulling this solution from my own experience in the past I've actually written macros in the past that solve for this however I haven't
posted those on the internet yet and I guess none of my fellow bloggers or YouTubers haven't either because chat gbt doesn't have this knowledge yet so now we can go ahead and run this macro I have already selected a few sheets back in the workbook and we'll just hit the Run button that's going to prompt us again for the export folder we'll hit okay you can see it starts starting to work there and we have three sheets exported we'll jump over to that folder and we can see those three uh files right here and here's
the CSV file so I'm now going to explain how to write a better prompt with my Oda prompting framework so here's what I consider a better or more thorough prompt I'm going to explain how to write this so the O and Oda stands for Overview at the top here I'm giving a brief explanation of the task or problem to help establish context the stands for details and here we want to give as much detail as possible about how the task should be solved and we can even use step-by-step instructions to just prevent assumptions or misunderstandings
and then the a stands for ask questions so here we can just simply ask any do you have any questions about this task or are there any potential problems that I might encounter with this task and I'll put a link in the description where you can download a PDF with this framework so here's the prompt in chat gbt and it came back with some great clarifying questions about the macro now one annoyance here is that you have to type out all of these answers I wish chat gbt had some kind of input form here or
multiple choice or something to make this process easier but one tip I have for you is it ask a lot of should questions and you can just copy this so in this case here where it's asking this question I'm just going to copy this I'm going to go down here and I've already uh input this line so I'm going to hit shift enter to add a new line I'm going to paste that right here here and then I'm just going to move the should so I'm going to just select should I'm going to just drag
it over before B and then now this answers the questions it says the CSV file should be named after the sheet name this doesn't work for every single question but as you can see there are a lot of should questions so here are all of my answers to those questions and of course chat gbt came back and rewrote the macro for us and it even added this function down here at the bottom to replace any invalid characters in the file name that might be in the sheet name this is nice and something I really didn't
think of when I started out with this macro now it still has an issue on the ws. copy line up here but I just went ahead and fixed that manually to get this macro to work now you might be thinking that we went through a lot of iterations here just to get this macro to work and you're absolutely correct but the advantage of chat GPT here is that we didn't have to write the entire macro it would take a long time to write out all of this code assuming we knew all of this code and
we likely have to do several Google searches in the process to try and figure out some pieces of this and with coding comes a lot of trial and air and chat gbt really helps us accelerate that process to help us fail faster so we can get working results quickly so tools like chat gbt can definitely help us automate Excel Microsoft has named it co-pilot and that's how I like to think about it it's a fantastic assistant that can help us get our job done faster but I do believe that we need to have the knowledge
of the tools within Excel like VBA to get the most out of it otherwise you're likely going to get frustrated and give up or create solutions that won't give you the best results we do have in-depth training on VBA here at Excel campus and I'll put links in the description to those resources now I'm curious to know how you're using these AI tools to automate your work so leave a comment below and let us know if you enjoyed the video hit the like And subscribe buttons and if you want to learn other ways to automate
Excel then check out this video next thanks again for watching have a great day and I'll see you in the next video [Music]
