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