Import Specific Cells From Google Sheets Excel VBA Macro

In a previous post we learnt to import data from Google Sheets using a web query in Excel (Import Data From Google Sheets). We can actually create a web query in Excel either manually or with a simple VBA macro. In this post we see another method to import data from Google Sheets that allows getting specific rows, columns, and cells or values depending on certain criteria. In this other method, we send a HTTP request to the Google spreadsheet URL to get the data, and then we loop through the HTML response table to extract the values of interest according to a chosen criterion.

Macro/VBA code:

 

  Sub GetGoogleSheetsDataAsHTMLTable()

      Dim key As String, url As String

      Dim HTTPreq As Object, HTML As Object

      Dim r As Integer, c As Integer

 

      Set HTTPreq = CreateObject("MSXML2.ServerXMLHTTP")

      key = "1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI" 'replace with yours

      url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key

 

      'send HTTP request to get the data

      With HTTPreq

          .Open "GET", url, False

          .send

      End With

      Do Until HTTPreq.readyState = 4: Loop 'waits till request completes

 

      'set HTML variable and assign response

      Set HTML = CreateObject("htmlFile")

      HTML.body.innerHTML = HTTPreq.responseText

 

      'loop through HTML table to get values (add conditions depending on criteria)

      For Each tr In HTML.getElementsByTagName("tr")

          If tr.getElementsByTagName("td").Length > 0 Then

              r = r + 1

              'If r <= 10 Then 'condition to get only first 10 rows

                  For Each td In tr.getElementsByTagName("td")

                      c = c + 1

                      Cells(r, c).Value = td.innerText

                  Next td

                  c = 0

              'End If 'could add Else to Exit For or Sub when done

          End If

      Next tr

  End Sub

 

Macro explained:

• First of all, we need to add the Microsoft XML v6.0 and Microsoft HTML Object libraries to the VBA project under Tools -> References in the Visual Basic editor.
• We declare HTTPreq as an object variable to later create and assign a ServerXML object to it (late binding). We do the same with the HTML object variable. We also declare some other variables to store the Google spreadsheet ID or key, the Google Sheets web page URL, and the row and column index variables used while looping through the response.
• The Google spreadsheet ID or key can be found after “/d/” and before the following forward slash in the Google Sheets sharing link (see below).

https://docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/edit?usp=sharing

• Note that the Google spreadsheet needs to be public and shared in Google Sheets settings (right-click the spreadsheet -> Share -> Get Link -> Anyone in the internet with this link can view).
• Then we add the key of the Google spreadsheet to the modified URL shown below, which is the reference to the Google spreadsheet output as an HTML table.

url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key

 

• Now we open an HTTP connection request using three arguments: The request method, in this case is a GET request, the target web page URL, and a third parameter indicating whether the call is asynchronous (False). After that, we send the request. The property readyState equals 4 when the request has successfully completed.
  
• Then we create a HTML document variable and define the innerHTML of the body as the HTTP response text. At this point, we can navigate the HTML table and get the values. For that we get the HTML elements by table row tags (tr) and then table cell tags (td). 
• We use r and c to indicate the corresponding row and column numbers of the table elements in the Excel sheet. 

The code above will actually get all the values (the whole table), as no conditions are applied. As an example, a commented line shows how to set a condition to get the first 10 rows only. The condition must be added before the second loop.

 

  If r <= 10 Then

     For Each td In tr.getElementsByTagName("td")

     …

 

 

Similarly, we could set a condition to get certain columns only. In that case, the condition must be inside the second loop.

 

  For Each td In tr.getElementsByTagName("td")

     c = c + 1

     If c = 1 And c = 2 Then    

         …

 

If we want to select various non-continuous columns, we could use another variable to differentiate between the source column in the Google spreadsheet table and the recipient column in the Excel worksheet.

This other example shows how to get rows when the value in the third column is 1 (only the first round of games in the example above). Note that we add “Or r = 1” to the condition to always get the header row.

 

  If tr.getElementsByTagName("td")(2).innerText = "1" Or r = 1 Then 

     For Each td In tr.getElementsByTagName("td")

         …

 

         

There are several other ways to get specific rows, columns, or cells from Google Sheets. We can use the Google Sheets API, the Google Visualization API, or create our own Google script/API. We will cover that in other posts.

This is how we import specific cells from Google Sheets into Excel with VBA macros.

 

Other examples:

• How to Unhide All Sheets And Cells with Excel VBA macros
  
• How to Separate Words And Numbers with Excel VBA macros
  
• How to Delete Rows Based On Criteria with Excel VBA macros