Excel VBA -next level: interact with clouds, services and AI

Name: Excel VBA -next level: interact with clouds, services and AI
Rating: 4.7 (9 reviews)

Using VBA in the modern scenarios, interact with Google and other services, Azure, big Data and AI

Created byDan Prodi, Pipi Rahayu

Last updated 6/2024

English

English [Auto],

What you'll learn

Connect Excel to Google Services via VBA
Google Apps Script for Google Sheets
VBA and OpenAI
Using VBA to manipulate data in an Azure Cloud
Using VBA to access amd manipulate big data

Course content

14 sections • 132 lectures • 11h 42m total length

Introduction3:22
Develop Excel VBA to interface with Google services, Azure clouds, REST APIs, and artificial intelligence; sync Excel with Google Sheets, import and export big data, and build REST API workflows.
What is the future of VBA4:40
VBA is a critical capability for large numbers of Microsoft customers.
Accordingly, there is no plan to remove VBA from future versions of Excel”.
But that was in 2008, way before the cloud storm closed in.
What is the commitment Microsoft is willing to give us in 2020+ about VBA?
Is it worth learning VBA?2:52
Even though today primarily Python and PowerQuery are used and are becoming more popular and efficient, can VBA still be needed?
Google Sheets vs. Excel: Which is right for you?13:44
Google Sheets and Excel are very similar
They often use the same formulas, and you can troubleshoot them in similar ways.
But each platform has unique strengths that appeal to different audiences.
Which one is for you? Maybe both?

Prepare your project in Google Dashboard7:32
Connect Google Drive and Sheets using VBA. Read, Write, Upload, Download, List files, Create, Delete, and more
Prepare your VBA code8:14
In this and the next videos we will see five different methods to import data from Google Sheets using Excel VBA.
One way or another, there is always a HTTP request to the Google web resource that connects with the Google spreadsheet.
The main difference between the various methods presented here has to be found in the Google web resource/API URL used to access the data.
Another important difference is the format of the output data. Some methods allow to get only public data, while other may also let import private or restricted data.
Case of Google Document with more sheets5:13
Retrieve data in public mode with a query11:21
Learn to retrieve data from a public URL in excel VBA by making an HTTP request with MSXML2.XMLHTTP, parsing an HTML table, and populating sheet two with rows and columns.
Retrieve data with the Google visualization API (part 1)3:41
Explore how to retrieve data from Google Sheets using the Google Visualization API, build a query, obtain a JSON response, and prepare it for Excel VBA.
Retrieve data with the Google visualization API (part 2)1:32
Encode query in Excel using encode URL, then retrieve data with the Google Visualization API and parse the JSON to access records.
Retrieve data using the Sheets API (part 1)7:19
Discover how to retrieve data from Google Sheets using the Sheets API by building a request with sheet ID, sheet name, range, and API key, plus authentication for restricted access.
Retrieve data using the Sheets API (part 2)2:37
Set up a lightweight Google authentication object with client ID, client secret, and scope to access the Sheets API, authenticate once, then import or export data.
Retrieve data using the Sheets API (part 3)2:20
Retrieve data from Sheets API by configuring the Google console with client ID, client secret, and API key, then grant permissions to access the data.
Retrieve data in protected Google Sheets (part 1)4:21
Retrieve data from a protected Google Sheets with Excel VBA by sending an http request using xml and reading the response text, demonstrating how to access restricted sharing data.
Retrieve data in protected Google Sheets (part 2)8:56
Application: import a JSON file into Excel6:22
Application: import a table from a Google Sheet in HTML format12:37
Import a google sheets table as HTML by building an HTML string in VBA, looping through rows and cells, and deploying a web API.

Prepare a Google Form first4:39
In this and the next videos we will see how to upload or export data from Excel to Google Sheets using VBA macros.
The VBA macro sends a HTTP request to POST the data through a Google Form into the Google spreadsheet.
For that reason, we need to add the Microsoft XML 6.0 library to the VBA project.
We also need to link the recipient Google spreadsheet to a Google Form, which needs to have as many fields as data columns to be exported from Excel.
Setup the Google Sheet and start your VBA code2:04
Publish the Google form to the web, copy the URL, and use a VBA subroutine with a web query to pull form data into Excel.
Retrieve the details from a Google Form3:52
Discover how to retrieve details from a Google Form with Excel VBA by inspecting the HTML, capturing the post URL and payload, and extracting name, address, and hidden fields.
Understand how to import Google Form data5:07
Create your VBA project to import Google Form data3:49
Define the url and parameters in the macro project, build a get request to import Google Form data into Excel, use a web query, and view responses.
Create a web service in VBA to import the data (part 1)2:58
In this and the next video we will see how to send encrypted data to a public google form to act as a central point to gather data. Also decrypting the data on the way back.
Create a web service in VBA to import the data (part 2)11:37
Post data from Excel to Google Sheets using VBA and MSXML 6.0, encoding parameters, setting headers, and sending an HTTP post to update the sheet.

Introduction4:44
Apps Script is a coding language designed to do more with Google applications e.g., Drive, Docs, Sheets, Calendar, Gmail, and more.
It is based on JavaScript and runs in the cloud rather than on your device.
It is helpful for automating things you might do in Google apps or finding creative solutions to problems.
Defining functions and variables4:52
Display messages2:24
Explore displaying messages in Excel VBA with MsgBox, and alerts via the UI object in JavaScript or Google sheet script; save, run, and import macros to execute sheet functions.
Design objects and assign events3:41
Design objects in Google Sheets by inserting shapes, then assign scripts to them, highlighting case-sensitive script names and the macro-enabled workflow for automation across sheets.
Recording macros5:29
Relative reference and absolute reference5:00
Format tables7:10
Generate charts3:55
Ranges and cells7:25
Format and reference ranges3:02
Learn to format and reference ranges in Excel VBA, set and get values using ranges and cells, and understand the object hierarchy in Google Sheets scripts.
Retrieving cells into ranges with the loop For instruction10:14
Move data from one sheet to another2:08
Creating custom functions5:59
Creating custom formulas8:36
Manage Events7:13
Clearing content and formats in ranges4:49
Discover how to clear content and formats in a Google Sheets range using Apps Script, choosing between clearing content, clearing formats, or both.
Using conditions (if, else,…)11:58
Learn how to implement conditions in JavaScript for Google Sheets apps, using if, else, and else if with loops, range values, and compare top with bottom to drive macro logic.
Arrays4:55
Objects8:52
Lookup functions6:44
Application of the lookup function5:49
Combine data from different sheets (part 1)9:09
Combine data from three sheets into a single result sheet in Google Sheets by extracting values from each sheet and placing them into a coordinated destination range.
Combine data from different sheets (part 2)6:19
Combine data from multiple sheets in Excel VBA by aligning columns, using variables to track progress, and stacking tables with array concatenation to create a unified table with headers.

Display a Google Map on an Excel file4:42
In this video, I will show how to display a Google Map on Excel using VBA.
Using the shell function to display Google maps via VBA1:23
Using VBA to retrieve the directions between two locations in Google Maps3:16
Using the Google Maps API to retrieve info via VBA6:22
Using the Google Maps API to retrieve the distance and duration of travel between two locations in VBA.
Get an API Key to run Google Maps services in VBA2:59
Create a Google Cloud project, enable Maps APIs such as Places and Directions, generate an API key for VBA, and parse the returned JSON values for use.
Retrieve the response from a request to Google Maps service via VBA7:43

Introduction to OpenAI1:36
Discover how to use artificial intelligence to generate VBA code and obtain an OpenAI API key, sign up on OpenAI, and apply AI to VBA projects.
Using OpenAI in VBA9:54
Learn to call the OpenAI API from VBA by configuring an API key, posting a JSON payload to the completion endpoint, and parsing the JSON response.
Using chatGPT to create VBA macro automatically4:02
Add the chatGPT Add-in into Excel3:20
Discover how to install and configure the ChatGPT add-in in Excel, enable the artificial intelligence assistant, enter your application programming interface key, and use built-in functions without VBA.
Generate insights with the chatGPT Add-in in Excel0:56
Use the ChatGPT add-in in Excel to generate insights from data, including headers, and see AI-driven analysis like identifying a 15% tax rate across A, B, C, E, and G.
Using the AI Assistant to translate text in Excel1:13
Use the AI assistant translator to translate text in Excel into languages such as Spanish, German, and Italian, enabling quick translation with ChatGPT-powered tools.
Using the AI Assistant to extract information from a text in Excel1:36
Explore how to extract information from a text string in Excel using the extractor function, pulling fields like names and locations to turn unstructured data into structured details.
Using the AI Assistant to retrieve missing data in Excel0:54
Use the AI assistant in Excel to fill missing data by selecting a range and providing a precise reference, retrieving entries such as Italy, Milan; Germany, Munich; or Lebanon, Beirut.
Using the AI Assistant native chatGPT function in Excel0:35
Discover how to use Excel's native chat function with the AI assistant ChatGPT to perform calculations and access AI help directly inside Excel.
Using the AI Assistant to write formulas in Excel2:33
Discover how to use the ChatGPT assistant to write Excel formulas, reference cells like A1, apply conditional logic, and adapt syntax such as semicolon versus colon.
Using the AI Assistant chat function with different parameters in Excel2:35
Explore using the ai assistant chat function in excel to input two values with locale-aware separators, save results, and recall previous calculations to quickly compute squares.
Create your Q&A in Excel with chatGPT1:38
Using chatGPT in Excel to explain functions1:15
Using VBA to apply AI to multiple cells9:02
Using VBA to create your own chatGPT in Excel11:51
Create your own chatGPT as custom function using VBA4:19
Convert a VBA subroutine into a ChatGPT-enabled custom function in Excel, with text input and an optional word count, enforcing a max word limit and trimming a trailing dot.

How to convert Macro enabled Excel file to Google Sheet1:43
This video will demonstrate how to convert macro enabled excel file to google sheet. macro converter add-on in google sheet.
Export Excel to Google Sheets2:44
U will show you how to access any data stored in Excel from Google Sheets.
It doesn’t even matter what version of Excel you have!
How to connect auto-synchronized Microsoft Excel with Google Sheets (part 1)8:57
In this and the next videos, you will learn how to connect Google Sheets with Microsoft Excel.
This is a one-time setup. After that, when you update the Google Sheets, the updates will be reflected in the Excel file.
How to connect auto-synchronized Microsoft Excel with Google Sheets (part 2)8:08
Learn to auto synchronize Excel with Google Sheets by importing CSV data via Google Apps Script, creating a sheet, fetching the CSV from Google Drive URL, and populating cells.
How to connect auto-synchronized Microsoft Excel with Google Sheets (part 3)2:06
How to connect auto-synchronized Microsoft Excel with Google Sheets (part 4)5:24
Learn to synchronize Google Sheets with an Excel file by importing web data, publishing the sheet to the web, and configuring automatic refresh every two minutes.
Auto-Synchronize Data from Excel to Google Sheets5:01
How to auto-synchronize data from Microsft Excel to Google Sheets.
This is for you if you have an Excel file on your computer, and you want to update the data to Google Sheets when you make some updates to the Excel file.
How to Download Google Sheet using Excel VBA5:46
How to Import Data from Webpages into Google Sheets (part 1)3:01
In this and the next video I will show you how to import tables and lists from a webpage to Google Sheets.
Using the IMPORTHTML function in Google Sheets allows this import to be done very quickly.
You can customize the data you import from the web by limiting columns or adding filters to get exactly what you want in Google Sheets.
This is done by adding the QUERY function along with the IMPORTHTML function in Google Sheets.
How to Import Data from Webpages into Google Sheets (part 2)3:46
Tools to synchronize MS Excel with Google Sheets3:24
Explore tools to synchronize Microsoft Excel with Google Sheets, enabling local and online collaboration, using Sheetgo, Zapier, Make, Copper.io, and related pricing.

Requirements

No programming experience needed, only basic knowledge of MS Excel

Description

In this course you will see if it makes sense to learn and use Visual Basic for Applications to create applications to exchange and manipulate data between Microsoft Excel and other services and new technologies e.g. clouds, big data (Power BI), APIs, Google Services and Artificial Intelligence.

In details we will see:

How to use VBA to export data From Excel to Google Sheets
How to use VBA and Apps Script to import data From Google Sheets to Microsoft Excel
How to run Google Services From Excel VBA
Apps Script, the VBA for Google Sheets (we will see the main functionalities of the scripting language for Google Docs)
How to use VBA to exchange information between Microsoft Excel and Google MAPS
How to use VBA to interact with your Google Calendar
How to use VBA to interact with Artificial Intelligence (chatGPT and OpenAI) and integrate AI functionalities on Excel files
How to use VBA to import and manipulate Big Data
Special Functions e.g.
- How to convert Macro enabled Excel file to Google Sheet,
- Export Excel to Google Sheets,
- How to Connect Microsoft Excel with Google Sheets, Auto-Sync Data from Excel to Google Sheets,
- How to Download Google Sheet using Excel VBA, How to Import Data from Webpages into Google Sheets, How To Create A Real-Time Sync Between Excel & Google Sheets
How to use VBA to access data on an Azure cloud
How to use VBA to access REST Web Services

Who this course is for:

Data Analysts
MS Office users
For those doing reporting
For teams working on clouds, google services and with office applications together

Excel VBA -next level: interact with clouds, services and AI

What you'll learn

Explore related topics

Course content

Introduction4 lectures • 25min

From Google Sheets to Microsoft Excel via VBA13 lectures • 1hr 22min

From Google Sheets to Microsoft Excel via VBA7 lectures • 34min

Macro in Google Sheets - Apps Script23 lectures • 2hr 20min

VBA and Google MAPS6 lectures • 26min

Google Apps Script and Google MAPS2 lectures • 16min

VBA and Artificial Intelligence: chatGPT and OpenAI16 lectures • 57min

VBA & Big Data3 lectures • 23min

Special Applications11 lectures • 50min

How To Create A Real-Time Sync Between Excel & Google Sheets4 lectures • 22min

Requirements

Description

Who this course is for: