Excel + ChatGPT Integration: The Ultimate Problem-Solving Duo

Aug 14, 2023

Excel + ChatGPT Integration: The Ultimate Problem-Solving Duo

Get ready to revolutionize your spreadsheet game! In this video, I’ll show you how to use the power of OpenAI’s ChatGPT right inside Microsoft Excel. With a custom VBA code, you’ll be able to integrate the cutting-edge AI technology directly into your spreadsheets. Whether you’re looking to automate tedious tasks or add a new level of intelligence to your data analysis, this video will take you through the process step by step.

🌎 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦:
VBA Code: https://github.com/Sven-Bo/Integrate-…
OpenAI API: https://openai.com/api/
Leils’s Office Script Video: • Bring ChatGPT INSIDE Excel to Solve A…

⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
0:00 – Introduction
0:33 – Disclaimer
1:00 – Creating a Travel Plan
1:28 – Generating Sample Data
1:53 – Generating Excel Formulas
2:32 – Generating VBA Code
3:36 – Demo of ChatGPT Integration
4:09 – How to Integrate ChatGPT in Excel
7:52 – Walkthrough of VBA Code
9:30 – Outro

◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘:
🌎 Website: https://pythonandvba.com
📝 GitHub: https://github.com/Sven-Bo
⭐ Discord: https://pythonandvba.com/discord
▶️ Subscribe: https://youtube.com/c/CodingIsFun?sub…

🎉 𝗙𝗥𝗘𝗘 𝗘𝘅𝗰𝗲𝗹 𝗔𝗱𝗱-𝗶𝗻 𝘁𝗼 𝗯𝗼𝗼𝘀𝘁 𝘆𝗼𝘂𝗿 𝗽𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝘃𝗶𝘁𝘆
Get it here: https://pythonandvba.com/mytoolbelt

📚 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗳𝗼𝗿 𝗹𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗩𝗕𝗔 \u0026 𝗣𝘆𝘁𝗵𝗼𝗻
Check out my recommendations: https://pythonandvba.com/resources

☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲❓
If you want to support this channel, you can buy me a coffee here:
https://pythonandvba.com/coffee-donation

Content

0.179 -> Hey guys, In this video, I'll show you how to take your

2.63 -> productivity in Excel to the next level by integrating ChatGPT.

7.08 -> By the end of this tutorial, you'll have a new button, ready to generate Excel formulas,

11.07 -> VBA code or, like in my example, a travel plan.

15.42 -> To use it, simply select the cell with your prompt, click the button, and ChatGPT will

20.38 -> generate a response that will appear in a new worksheet.

23.119 -> I'll also be sharing some practical use cases for this tool and guide you through the process

28.46 -> of adding this button to your Excel files.

30.71 -> So, get ready to boost your Excel workflows with ChatGPT!

34.48 -> Before we begin, a short disclaimer: This video is heavily inspired by Leila's video.

39.45 -> She wrote an Office Script to bring ChatGPT into Excel, and I thought to myself, 'Hey,

44.37 -> I can do that too... but with VBA!'

46.379 -> So, that's the reason I created this video.

49.53 -> If you're interested in the Office script solution, then definitely check out her video.

53.76 -> I'll leave the link to the video in the description below.

57.03 -> And with that out of the way, let's begin with the first example.

60.73 -> As an Excel nerd, I even use spreadsheets to plan my vacations.

63.59 -> With the help of ChatGPT, I can now speed up the vacation planning process.

67.99 -> In this example, I asked ChatGPT to create an itinerary for a day trip to Barcelona,

73.64 -> Spain.

74.64 -> After clicking the button, you will see in the status bar that your request is being

78.119 -> processed.

79.29 -> Once completed, a message box will appear, and a new worksheet will be inserted.

84.51 -> On the results sheet, you will now find your itinerary for the day trip.

89.7 -> Next up, I am going to generate some sample data, which I will use in our next example.

94.899 -> For this purpose, I want to create some fictional employee IDs that have a particular format.

101.079 -> Once again, when I click the button, the output will appear on the result sheet.

105.99 -> As I said, we will use this data in the next example, so let me create a new sheet and

111.29 -> paste the data here.

114.27 -> Suppose you want to clean up the data and extract only the numbers from the ID, but

119.409 -> you are unsure which Excel formula to use.

122.59 -> You can now turn to ChatGPT for help.

125.259 -> I provided an example of the format and asked for an Excel formula to extract the numbers.

131.84 -> After clicking the button, the formula will appear on the results sheet.

136.22 -> Let me copy this cell and paste it next to the ID.

138.53 -> I only need to update the cell reference, so instead of B1, the data is located in A3.

146.48 -> Once that is done, I can simply drag the formula down, and now I have the cleaned ID.

152.31 -> Instead of Excel formulas, you can even create entire VBA codes.

156.38 -> For this example, I want to split each worksheet in the current workbook into separate Excel

161.319 -> files and save them on my desktop.

163.91 -> When I click the button, I receive the VBA code in the results sheet.

168.9 -> All I have to do now is to run the code.

171.239 -> So, let me copy the code, go to the Developer tab, and open the Visual Basic editor.

178.52 -> In a new module, I will paste the code.

181.36 -> Just by looking at this, I can already see that VBA won't accept the double backslashes.

186.5 -> This could be because the API response from OpenAI is in JSON, and I might need to implement

192.36 -> an additional cleaning step.

193.93 -> Nonetheless, I'll try to execute it.

197.099 -> As I said, this code currently leads to an error, but to fix it, all you need to do is

202.409 -> adjust the file path.

203.54 -> Now, when I rerun it, the worksheets will be saved as separate workbooks on my desktop.

210.01 -> To validate it, let me open up one of the spreadsheets.

213.18 -> Here we have our Employee data.

215.269 -> Now, the cool thing is that the ChatGPT functionality is available in all my workbooks.

221.26 -> It doesn't need to be a macro-enabled workbook.

224.5 -> Let me show you what I mean.

226.26 -> You can pick any cell you want.

228.439 -> For example, I will ask ChatGPT for a joke.

231.76 -> Next, all I need to do is select the cell and then click the smiley emoji up here.

236.819 -> Once done, a new Results worksheet will appear, and I can find the joke there.

243.59 -> By the way, if you select an empty cell and try to run it, you will get the following

247.98 -> error message.

250.14 -> Now that you've seen how it works let me show you how to add this functionality to Excel.

255.14 -> All you need is an OpenAI API key.

258.57 -> Go to the following website; you will find the link in the description below.

262.7 -> Next, create a free account.

265.25 -> No credit card is required.

267.12 -> When signing up and creating an API key, OpenAI will grant you $18 in free credit that can

273.5 -> be used during your first three months.

275.91 -> After that, you would need to pay to use their service.

279 -> Ok, and with that in mind, log in to your account, and in the upper right corner, click

284.691 -> on "Personal" and select "View API keys".

289 -> On this page, click on "Create new secret key".

291.919 -> Now, it is important that you do not expose this API key.

296.81 -> Just for the sake of this video, I will show you my API key.

299.91 -> However, when you watch this video, this key will no longer be valid.

305.1 -> With that said, let me copy this API key and put it temporarily in my browser.

309.539 -> We will come back to this API key in just a moment.

313.6 -> Next, open up a new Excel workbook and then click on "Developer".

318.669 -> If you do not see the "Developer" tab, right-click on your ribbon and select "Customize Ribbon".

324.71 -> Ensure that you tick the box next to Developer and confirm your selection by clicking OK.

330.8 -> With that in place, you can now click on "Record Macro".

334.46 -> It is important that you select "Personal Macro Workbook" in the drop-down field here.

339.76 -> The name and description do not matter; Next, click on OK.

343.889 -> Right after that, you can stop the recording by hitting this button.

348.21 -> Now, open up the Visual Basic Editor.

351.28 -> If everything went well, you should now see a new section called "Personal.xlsb".

357.32 -> Open this section and navigate to "module", and open "Module1".

361.979 -> In this module, we will paste the VBA code I uploaded to GitHub.

365.93 -> You will find the link to the GitHub repo in the description of the video.

369.199 -> If you click on it, you will see this site.

372.53 -> All you need to do is click on the copy icon up here.

375.89 -> Then, navigate back to the Visual Basic editor, select all existing code, and replace it with

382.13 -> the code you just copied.

384.69 -> On the very top of this script, you will find a section where you need to input your API

389.77 -> key.

390.77 -> So, in my case, I pasted the API into my browser.

395.479 -> Let me go back, grab the API key and paste it here.

399.87 -> Next, I just need to assign this macro to the Quick Access Toolbar.

404.71 -> Therefore, in Excel, click on the arrow key in the top left corner and select "More Commands".

411.819 -> In the new window, select "Macros" and add the macro from the Personal Workbook to your

416.879 -> Quick Access Toolbar.

418.55 -> Optionally, you can also modify the button icon.

422.62 -> Once done, click "OK".

424.72 -> When you close the workbook, you will get a message asking you to save the changes to

429.37 -> the Personal Workbook.

431.08 -> It is important to click on "Save" to make the VBA code available in all your Excel files.

436.789 -> To test it, let me open a new spreadsheet.

439.449 -> I can now simply enter my prompt in any cell, select that cell, and run the VBA code from

445.51 -> the Quick Access Toolbar.

447.74 -> The response from ChatGPT will appear in a new worksheet.

451.919 -> And if you want to create a workbook similar to the one I showed you in the beginning,

455.97 -> you can also execute the macro by clicking a button.

459.759 -> To do this, draw a shape on your sheet, right-click on the shape, select "Assign Macro," choose

467.61 -> "Personal.xlsb" from the drop-down menu, and select the "OpenAI Completion" macro.

473.36 -> And for those who are interested in how this works, let me briefly explain the VBA code.

478.15 -> Essentially, it sends an HTTP request to the OpenAI API.

483.18 -> You can also adjust the API endpoint parameters, which are detailed in the OpenAI documentation.

489.36 -> In the code, I first check if you have inserted your API key.

493.699 -> If so, it checks if the current cell is not empty.

496.659 -> If it's not empty, the VBA code creates a result worksheet if it doesn't already exist.

502.11 -> To inform the user that the code is running, the status bar is updated before the HTTP

507.65 -> request to the OpenAI API is created.

511.52 -> After sending the request, the code checks the status code.

514.729 -> A status code of 200 means the request was successful.

519.469 -> As I mentioned before, the response is in JSON format, so I am cleaning up the response

524.18 -> text and inserting each line in a separate row.

527.779 -> Once this is complete, the new sheet is activated, and the user is informed via a message box.

534.529 -> If the status code is not 200, the user is notified via a different message box.

539.899 -> In case of any error, the error number, description, and line number are displayed in yet another

546.01 -> message box.

547.07 -> In addition to the main sub, there are helper functions used to check if the output worksheet

552.94 -> already exists, parse the JSON response, clean the input prompt for the cell, and remove

559.48 -> unwanted backslashes in the response from ChatGPT.

562.4 -> Okay, guys, that's pretty much all the code.

565.61 -> I hope you found this solution helpful.

567.87 -> If you did, please let me know by hitting the like button.

570.99 -> And as always, thank you for watching, and I'll see you in the next video.

Source: https://www.youtube.com/watch?v=-3otazH5crw