Connect To API Excel VBA Macro (Part 1)

In this post we learn how to get data from a web API in Excel using VBA macros. As an example, we are getting data form a public API with information about universities around the world. This API does not require any API key to be used, and consists of a simple URL that accepts a single parameter to specify the country of interest. The Excel VBA macro code to send an API call and retrieve information or data from the API consists of two steps. In this post we will cover the first step, which consists of sending an HTTP request to the API URL, in a similar way we have done to scrape web data in a previous post (Scrape Web Data in Excel Part 1). In the next post we will see how to read the API response returned as JSON format, and retrieve the list of universities for a given country.

Sort Elements In Array Excel VBA Macro

In a previous post we have seen how to sort text or words inside a single cell in the Excel worksheet using VBA macros. But that approach worked only with a list of unique values. In this post, we see how to sort the elements in the array created from the cell content, regardless whether that content has unique or repeated values. In both cases, the Split VBA function is used to convert the text or values into an array of elements. In this example, the elements are words separated by a space, but the same method can be used for values separated by a comma, semicolon, or a line break, as in the second scenario of the previous post.

Sort Text In Cell Excel VBA Macro

In this post we see how to sort the text or words inside a single cell in the Excel worksheet using VBA macros. This may come handy when dealing with a bunch of text imported or scraped (also copy/pasted) into a single cell, which we may need to sort or handle in some way. The process consists of converting the text into an array that allows to count the number of words, sort the words in alphabetical order, and do other manipulations of the text such as extracting the words into a range of cells. The macro example below is used to sort the text inside a cell alphabetically. The instructions underneath explain how to perform other actions. Another option is to sort elements in the array itself - see this other post.

Scrape Web Data Excel VBA Macro (Part 2)

In the previous post we have seen how to send an HTTP request to get web data as text (Part 1). In this post we see how to process the response into the body of a html document to target the data of interest (Part 2). We need to add the Microsoft HTML Object Library to the references in order to manipulate the response as a html document. Then we need to target and get the elements with the information we are after. In this particular example, that’s the job title, company, location, etc. Despite the macro is entirely written with Excel VBA, basic HTML/web programming skills are required to understand the whole process. 

Scrape Web Data Excel VBA Macro (Part 1)

In this post we learn how to send a HTTP request to scrape and import web data into Excel using VBA macros. As an example, we are getting data form a jobs portal, which contains public data that anyone can see on the browser with no login needed. The process consists of two steps. In this post, we will see the first step, which consists of sending a HTTP request to get the web page contents as text. In the next post we will see how to process the HTTP response into the body of a HTML document in order to target the data of interest.

Group Rows With Same Format Excel VBA Macro

In this post we see how to group cells or rows with the same format in Excel using VBA macros. Grouping cells or rows in Microsoft Excel is quite helpful when dealing with lots of data arranged in categories, phases, etc., which we may need to monitor or keep updating regularly. Then, we can collapse groups when complete or when not relevant at a given time, thus making space and setting focus on the target data. The Excel VBA macro loops through the dataset, and groups rows based on the format, specifically, the example below groups rows sharing the same category or headers with bold text. That can be changed to any other format condition such as font size, color, etc.

Compare Two Columns Excel VBA Macro

In a previous post we saw how to Compare Two Worksheets in Excel using VBA macros. In this post we see how to compare two columns with data. Each column is treated as a list of items, and the goal is to compare which items or values are not in common. Thus, the macro compares the two lists or columns in the active sheet, and finds the discrepancies between the two. In this macro example, the discrepancies are highlighted in yellow and also added to another sheet. The lists are supposed to have unique entries, but the macro also works is entries are repeated. In that case though, all of them will be highlighted and also added to the other sheet, thus having repeated terms there. The macro can be easily tweaked to avoid that and show only unique entries if used to compare two columns or lists with repeated entries.

Protect Selected Workbooks Excel VBA Macro

In this post we learn how to protect all selected workbooks in Excel using VBA macros. The macro protects the workbook structure and the contents of each worksheet with a password. Alternatively, or additionally, another line of code can be added to encrypt the file with that password. The macro initially asks the user to set a password, and then displays a dialog to select the files. The same code can easily be adapted to unprotect the workbooks with a password.

Import Data From Google Sheets Excel VBA Macro

In this post we see how to import data from Google Sheets in Excel using VBA macros. The macro uses Excel’s web query functionality to import the Google spreadsheet as a query table. The principle is similar to what we have seen in a previous post (Add Web Query To Worksheet). The Google spreadsheet to be imported needs to be public and accessed through a modification of the original link along with the key of the spreadsheet.

Show Chart For Selected Row via Hyperlink Excel VBA Macro

In this post we learn how to add a chart to show the data in the selected row using Excel VBA macros. We add a hyperlink to each row first, which triggers the procedure to show the chart. Thus, the macro consists of two procedures, one to add the hyperlinks, and another to add a chart every time a hyperlink is clicked. Additionally, the macro requires two event procedures to allow following the hyperlink, and to remove the chart when any other cell is selected. That makes the chart show up for each selected row each time.

Sort And Merge Groups In Table Excel VBA Macro

In this post we see how to sort and merge a group of cells with the same value in Excel using VBA macros. Selected values are sorted in ascending order, as per the value in column A first, and then column B. Then, the groups of contiguous cells with the same value are merged.

Add Buttons To Delete Rows Excel VBA Macro

We have already seen how to add buttons to a specific range or cell in Excel using VBA macros (Add Buttons To Fit Selection). In this post, we are going to add buttons to each row with data in a worksheet, and assign another macro to each of them to delete the respective row when clicked. Thus, there is a first macro to add the buttons, and a second macro to delete rows.

Compare Two Workbooks Excel VBA Macro

In this post we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares the worksheet names and the contents in the sheets with the same name (this last piece of code has been covered in this other post: Compare Two Worksheets). Whenever there is a mismatch, it highlights the sheet or cells with different values in yellow, and displays a message with cell and worksheet (this could also be copied to a separate sheet instead). Note that the macro compares the workbook with the macro (ThisWorkbook) and a second open workbook. Optionally, a dialog to open the second file, or even the two workbooks could be implemented at the start if need be.

Copy/Paste Charts To PowerPoint Excel VBA Macro

In this post we learn how to copy/paste charts from Excel to PowerPoint using VBA macros. The macro copies all embedded charts in a worksheet, and pastes each of them in a separate slide in PowerPoint as a picture.

 

Update Web Query On File Open Excel VBA Macro

In a previous post we saw how to import data from a webpage into a web query table in Excel using VBA macros. In this post, we see how to update or refresh an existing web query table when the file opens. The macro updates the COVID numbers for different countries as per the table in the worldometers website every time the file is open. That could be changed to update the number when a button is clicked, or using any other trigger.

Insert Picture From Google Drive Excel VBA Macro

In a previous post we saw how to insert pictures from the web in Excel using VBA macros. In this post we see how to insert pictures specifically from Google Drive, which requires the Google Drive view path and the picture sharing ID. The picture will be inserted in the selected range and resized to fit in accordingly.

AutoFill Table With SpecialCells Excel VBA Macro

In a previous post we have seen how to autofill table gaps in Excel using VBA macros. In this post we see another way to do it with less code. This macro example uses the CurrentRegion property to select the table, the SpecialCells property to target the gaps only, and the Offset property to get the value to be filled.

Resize All Charts In Worksheet Excel VBA Macro

In this post we learn how to resize all charts in the active Excel worksheet at once using VBA macros. The macro also places each chart in a particular position relative to the previous chart, starting from the top left corner of the worksheet. The size of the charts can easily be adjusted changing the values for the Width and Height properties.

Split Text Into Columns Excel VBA Macro

In this post we see how to split text into columns in Excel using VBA macros. The macro splits the text in one column into as many columns as dictated by the text separator (a comma, space, etc). In this example, we use the TextToColumns method of the Range object. But this can also be achieved using the Split VBA function to create an array and then write into columns.

Insert Picture From Web URL Excel VBA Macro

In this post we learn to insert pictures from the web in Excel using VBA macros. The macro inserts the picture in a given range and resizes the picture accordingly, so mind to select an appropriate range. Otherwise, specific position coordinates for the picture can be set instead. We can also decide whether to insert the source information or just make an independent copy of the picture.