How to Import Data from Google Sheets to Excel Using VBA

Undoubtedly, Microsoft Excel and Google Sheets are two of the most useful and popular tools for data analysis. Nowadays, Google Sheets is very popular for syncing data. If you’re an Excel user, most likely you know the methods of sharing the Excel file online. However, you may need to extract data from Google Sheets to Excel for various purposes. In this article, I will show you 3 easy and quick steps to import data from Google Sheets to Excel using VBA code. In another article, we discussed two methods to share the Excel file online.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


3 Steps to Import Data from Google Sheets to Excel Using VBA

For ease of understanding, we are going to utilize a dataset on Google Sheets. The sheet’s name is Importing Data to Excel. This dataset includes the Sales Rep, Product Name, Unit, and Sales in columns B, C, D, and E consecutively.

import data from google sheets to excel vba

Now, we’ll use this dataset above to import data from Google sheets to Excel using VBA. So, let’s go through it step-by-step to understand it clearly.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Step 01: Change Privacy of Google Sheets

At the very beginning, we have to change the privacy of the sheet so that anyone can get access to it. So let’s begin.

📌 Steps:

  • First of all, click on the Share button with a picture of a lock at the right top corner of the display.

Change Privacy of Google Sheets

Immediately, it opens the Share “Importing Data to Excel” dialog box.

  • Here, click on the drop-down arrow under the General access section.
  • Then, select Anyone with the link from the options.
  • After that, click on Done.

Making the Google Sheets Accessible for everyone

Currently, this sheet becomes public. Anyone with the link can access this sheet. So, our first part of the work is done.

Read More: How Can I Link Google Sheets to Excel in Real Time?


Step 02: Insert VBA Code to Import Data to Excel

Now, we’ll build a VBA code and execute it to extract the data from Google Sheets into our Excel workbook. So, without further delay, let’s see how we can do it.

📌 Steps:

  • At first, open a new workbook on Excel.
  • Next, go to the Developer tab.
  • Then, click on Visual Basic in the Code group.

Insert VBA Code to Import Data to Excel

Instantly, the Microsoft Visual Basic for Applications window appears before us.

  • Presently, move to the Insert tab.
  • Therefore, click on Module from the available options.

Inserting Code Module

Suddenly, it creates a code module where we can write our code to import data from Google Sheets to Excel.

  • Now, paste the following code into the module.
Sub Import_Sheets_to_Excel()
Dim QRT As QueryTable, ul As String, ky As String
If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
ActiveSheet.Cells.Clear
ky = "1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs"
ul = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & ky
Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _
Destination:=Range("$A$4"))
With QRT
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.Refresh
End With
End Sub

VBA Code to import data from google sheets to excel

Now, we’ll explain the VBA code.

Code Breakdown
  • Firstly, we declared the different variables we’re using in this script.
  • Then, we give an If logical statement to clear the query table in our Excel sheet. If there is any table or data in the cells in the worksheet, then they’ll be cleared. Because the newly imported data will take that place.
  • After that, we set the ky variable. Actually, it’s the Google spreadsheet key. We can obtain this key from the sheet’s sharing link on the address bar of the browser. We can locate it after “/d/” and before the next forward slash. See the address below to get it clearly.

https://docs.google.com/spreadsheets/d/1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs/edit#gid=0

Here, the bold part is the spreadsheet key.

  • Now, we’ll get the output of the Google spreadsheet data as an HTML table. For this reason, we’ll use a modified URL like the following.

“https://spreadsheets.google.com/tq?tqx=out:html&key=” & ky

  • After that, we added the query to the Excel sheet and also defined the destination range which is cell A4 in our case.
  • Hence, Run the code by clicking on the green-color play button on the ribbon.

Run the VBA Code to import data from google sheets to excel

  • Then, get back to the worksheet Sheet1.

You can see that Excel is getting the data from an external source.

Getting Data from Google Sheets to Excel

All the data appears very soon because our dataset is tiny in size. So, it takes a minimum amount of time to fetch the data.

Extracted Data from Google Sheets

Read More: How to Use QUERY Function of Google Sheets in Excel (3 Common Ways)


Step 03: Apply Formatting to Dataset to Make It Engage

The data we imported from Google Sheets doesn’t have any type of formatting. So, we’ll apply different types of formatting to it to make it more appealing and soothing for the eyes. So, let’s see it in action.

📌 Steps:

  • Firstly, we gave a relatable heading to the dataset in cell B2. It’s formatted in Heading 2 Cell Style.

Apply Formatting to Dataset to Make It Engage

  • Secondly, select cells in the B4:E14 range.
  • After that, go to the Home tab.
  • Then, click on the Middle Align icon and the Center icon on the Alignment group of command.

Applying Center and Middle Alignment

Now, it looks like the following.

Applying All Borders to the dataset

  • Lastly, select the headings in the B4:E4 range and make them Bold. Also, change the Fill Color and Font Color.

Apply Formatting to Imported data from Google Sheets to Excel to Make It Engage

Read More: How to Transform Google Sheets to Excel Automatically


Conclusion

This article explains how to import data from Google Sheets to Excel using VBA in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo