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.
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.
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.
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.
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.
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
Now, we’ll explain the VBA code.
- 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.
- Then, get back to the worksheet Sheet1.
You can see that Excel is getting the data from an external source.
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.
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.
- 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.
- Also, give All Borders to the selected range.
Now, it looks like the following.
- Lastly, select the headings in the B4:E4 range and make them Bold. Also, change the Fill Color and Font Color.
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.
How do I import the data from other sheets other than the sheet1 of googlesheet?
Hello, OBOT!
Thanks for your comment!
To import data from a specific sheet (e.g., “Sheet2“) of a Google Sheets document to Excel using VBA, you can use the following code:
To use this code, you need to replace the googleSheetsURL variable with the URL of the Google Sheets document containing the data you want to import and replace the sheetName variable with the name of the sheet containing the data you want to import (in this example, “Sheet2“). You also need to set the targetRange variable to specify the cell or range where you want to paste the imported data (in this example, cell A1 of the Sheet1 worksheet).
The code uses the MSXML2.XMLHTTP object to send an HTTP request to the Google Sheets document, and parses the HTML response to identify the range of cells corresponding to the specified sheet name. It then copies the data from the identified range to the clipboard, clears the target range to ensure that no existing data interferes with the import, and pastes the data from the clipboard into the target range.
Hope this will help you!
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.
Hello dear
Thanks you…
Is it possible to write a VBA program to upload data from an Excel file directly to Google Sheets?
Hello REZA,
Thanks for commenting and asking your valuable question. Actually, it’s quite costly to do it with VBA. Because of this, you have to enable the Google Sheets API, and quite expensive. So I wouldn’t suggest it.
Rather, you can use Google Drive or the Import feature of Google Sheets to do this task easily. You can follow this linked article on our website to get the whole idea.
Anyway, if you want to do it with VBA, you can follow this lengthy process:
Just make sure to change specific things in your own code. Hope this could help you. Again, thanks to you.
Regards
SHAHRIAR ABRAR RAFID
Team ExcelDemy
this doesnt work
Hello ROBERT,
Do you have Google API? I think the code works. But if it doesn’t work for you, you can use the simple Import feature which I mentioned in the previous reply.
Even if you want to do it with VBA, you can do it through Google Forms. This process doesn’t require the API key.
If you have further queries, you can comment below. Thanks for your feedback.
Regards
SHAHRIAR ABRAR RAFID
Team ExcelDemy
Hello – this is amazing information. I ahve been strugling to get it to work for ages. Can i shoose which rage to extract from the google sheet?
Hello GEORGI,
Thanks for appreciating our work. We are also happy to release you from prolonged suffering. Now, get back to your query.
Sure, you can alter the “Destination” parameter of the “QueryTables.Add” method in your script to specify a specific range to extract from the Google sheet.
For instance, you could change the code such that it retrieves data from cells A1 to E10 of the sheet:
The “ul” variable in this modified code has the addition “&range=A1:E10” to identify the range to extract from. Also, the starting cell where the extracted data will be stored is specified by the “Destination” parameter in the “QueryTables.Add” method, which is set to “$A$4“.
I hope this helps. I wanted to personally invite you to check out our new Excel-related forum. We’ve created a space for Excel enthusiasts like us to share tips, tricks, and ideas, as well as to ask and answer questions about using Excel. We’re a growing community of Excel users, and we’d love to have you join us!
Here’s the link to our forum: ExcelDemy Forum
Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy