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

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
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
Retrieve data with the Google visualization API (part 1)3:41
Retrieve data with the Google visualization API (part 2)1:32
Retrieve data using the Sheets API (part 1)7:19
Retrieve data using the Sheets API (part 2)2:37
Retrieve data using the Sheets API (part 3)2:20
Retrieve data in protected Google Sheets (part 1)4:21
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

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
Retrieve the details from a Google Form3:52
Understand how to import Google Form data5:07
Create your VBA project to import Google Form data3:49
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

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
Design objects and assign events3:41
Recording macros5:29
Relative reference and absolute reference5:00
Format tables7:10
Generate charts3:55
Ranges and cells7:25
Format and reference ranges3:02
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
Using conditions (if, else,…)11:58
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 different sheets (part 2)6:19

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
Retrieve the response from a request to Google Maps service via VBA7:43

Introduction to OpenAI1:36
Using OpenAI in VBA9:54
Using chatGPT to create VBA macro automatically4:02
Add the chatGPT Add-in into Excel3:20
Generate insights with the chatGPT Add-in in Excel0:56
Using the AI Assistant to translate text in Excel1:13
Using the AI Assistant to extract information from a text in Excel1:36
Using the AI Assistant to retrieve missing data in Excel0:54
Using the AI Assistant native chatGPT function in Excel0:35
Using the AI Assistant to write formulas in Excel2:33
Using the AI Assistant chat function with different parameters in Excel2:35
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

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
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
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

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: