🔢 Automate Data Retrieval from Google Sheets Using VBA UserForm

---

Ever wished you could pull up student details, inventory info, or client records with just a unique number? This blog walks you through how to build a VBA UserForm in Excel that fetches data from a Google Sheet row based on a unique ID—then auto-fills text boxes with the matching data.

---

🎯 Use Case Scenario

Suppose you’re maintaining a student database in a Google Sheet. You want to create an Excel-based form where entering a Student ID will instantly display the student’s name, class, and contact info. No manual lookup needed.

---

🧱 Step-by-Step Process

1. 🌐 Prepare Your Google Sheet

• Organize data in rows where column A has unique IDs (e.g., student or employee numbers).
• Share the sheet publicly or via API access for Excel to read it.

📌 Tip: Use Google Sheets API or publish the sheet to web (File > Share > Publish to web) for basic use.

---

2. 📤 Export Google Sheet as CSV (for quick integration)

For a simple workaround:

• Publish your sheet to the web.
• Copy the CSV link:
  Example format:
  https://docs.google.com/spreadsheets/d/{SHEET_ID}/export?format=csv&id={SHEET_ID}

---

3. ⚙️ Create a VBA UserForm in Excel

1. Press Alt + F11 to open the VBA Editor.
2. Insert a UserForm and add:
   • A TextBox for Unique ID input
   • Several TextBox controls for other data fields (Name, Class, Contact, etc.)
   • A CommandButton to trigger search

---

4. 📄 Add VBA Code to Fetch CSV Data

Here’s a simplified version of how to load the Google Sheet into Excel using VBA:

Sub LoadGoogleSheetData()
    Dim URL As String
    Dim httpRequest As Object
    Dim ws As Worksheet

    URL = "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/export?format=csv"

    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "GET", URL, False
    httpRequest.Send

    If httpRequest.Status = 200 Then
        Set ws = ThisWorkbook.Sheets("Sheet1")
        ws.Cells.Clear
        Dim arr As Variant
        arr = Split(httpRequest.responseText, vbLf)
        Dim i As Long
        For i = 0 To UBound(arr)
            ws.Cells(i + 1, 1).Value = arr(i)
        Next i
    Else
        MsgBox "Failed to retrieve data"
    End If
End Sub

This code loads the Google Sheet content into Sheet1. You can call this subroutine before or within your UserForm logic.

---

5. 🔍 Search for the Unique ID and Fill TextBoxes

Now let’s write the code to run when the user clicks “Search” in the form:

Private Sub cmdSearch_Click()
    Dim id As String
    Dim lastRow As Long, i As Long
    Dim ws As Worksheet

    id = txtID.Value ' txtID is the input TextBox
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = id Then
            txtName.Value = ws.Cells(i, 2).Value
            txtClass.Value = ws.Cells(i, 3).Value
            txtContact.Value = ws.Cells(i, 4).Value
            Exit Sub
        End If
    Next i

    MsgBox "ID not found!"
End Sub

---

✅ Features Covered

• Fetch data dynamically from a Google Sheet
• Search using a unique key (like ID)
• Auto-fill multiple fields in a VBA UserForm
• Handle missing or unmatched entries

---

🔚 Conclusion

Using VBA with Google Sheets opens up a world of data connectivity—even outside Excel. This method is ideal for dashboards, lookup forms, and offline data access. Once set up, users simply enter a code and see the details—no formulas, no scrolling. Just clean, interactive data access.

---