VBA code for downloading Excel files from Google Drive shows format errors

[Emma_Galaxy]

I’m working on a VBA script to fetch an Excel spreadsheet from Google Drive but running into some issues. When the file downloads to my local folder, it contains strange additional text at the top of the first worksheet. Plus I keep getting an error message saying the file format doesn’t match what Excel expects. After clicking through that warning, another popup appears about missing CSS files.

The actual data I need is there but it shows up below all this extra unwanted content. Has anyone else experienced this problem when downloading Google Sheets as Excel files through VBA? What causes these format issues and how can I fix them?

Dim FileHandle As Long
Dim DataBytes() As Byte
Dim TargetURL As String
Dim HTTPClient As Object

On Error Resume Next
    Set HTTPClient = CreateObject("WinHTTP.WinHTTPrequest.5")
    If Err.Number <> 0 Then
        Set HTTPClient = CreateObject("WinHTTP.WinHTTPrequest.5.1")
    End If
On Error GoTo 0

TargetURL = "https://docs.google.com/spreadsheets/d/1abc123xyz789/edit?usp=sharing"

HTTPClient.Open "GET", TargetURL, False
HTTPClient.send
DataBytes = HTTPClient.ResponseBody
Set HTTPClient = Nothing

If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"

FileHandle = FreeFile
Open "C:\MyDownloads\output.xlsx" For Binary As #FileHandle
    Put #FileHandle, 1, DataBytes
Close #FileHandle

MsgBox "Check folder C:\MyDownloads for your file"

[MiaDragon42]

yeah, i had the same prob. ur code pulls the whole webpage, so those texts are just HTML markup. just swap /edit?usp=sharing to /export?format=xlsx in the URL, then u should get just the xlsx data without all that junk.

[OwenNebula55]

You’re downloading the HTML page, not the actual Excel file. That’s why you’re seeing all the formatting and CSS stuff. The /edit?usp=sharing URL gives you the web interface, not the raw data. Change your TargetURL to something like TargetURL = "https://docs.google.com/spreadsheets/d/1abc123xyz789/export?format=xlsx". The export parameter tells Google Drive to serve the actual Excel file instead of the web viewer. I ran into this same thing last year and wasted hours debugging before I figured out it was just the URL. That export bit makes all the difference.

[wanderingWeasel]

The Problem:

You’re trying to download an Excel spreadsheet from Google Drive using a VBA script, but the downloaded file contains extra text at the top of the first worksheet, along with error messages about file format mismatches and missing CSS files. The actual data is present, but it’s below this unwanted content. This indicates that your script is downloading the Google Sheets webpage instead of the raw Excel file data.

TL;DR: The Quick Fix:

Change your TargetURL to use the Google Sheets export endpoint instead of the edit endpoint. Replace:

TargetURL = "https://docs.google.com/spreadsheets/d/1abc123xyz789/edit?usp=sharing"

with:

TargetURL = "https://docs.google.com/spreadsheets/d/1abc123xyz789/export?format=xlsx"

This will download the spreadsheet directly as an .xlsx file, avoiding the HTML and CSS elements that cause the errors.

Understanding the “Why” (The Root Cause):

The original URL (/edit?usp=sharing) directs the browser to the Google Sheets online editor. Your VBA script is downloading the HTML code of this web page, which includes the visual elements, formatting, and CSS styles. This is why the downloaded file contains extra text and reports errors related to missing CSS and format mismatches. The /export?format=xlsx URL, on the other hand, instructs Google Sheets to directly export the data in the .xlsx format, bypassing the HTML rendering process.

Step-by-Step Guide:

1. Modify the Target URL: Open your VBA script and locate the line defining TargetURL. Replace the /edit portion of the URL with /export?format=xlsx. The corrected line should look like this:

TargetURL = "https://docs.google.com/spreadsheets/d/1abc123xyz789/export?format=xlsx"

Ensure the spreadsheet ID (1abc123xyz789) is correct.

2. Test the Modified Script: Save your changes and run the script again. The downloaded output.xlsx file in the C:\MyDownloads folder should now contain only the spreadsheet data without the extra HTML content.

3. Implement Error Handling (Optional but Recommended): Enhance the script by adding error handling to check the HTTP request status code. If the request fails (e.g., returns a 404 error), display an informative message instead of silently failing. Example:

' ... existing code ...

HTTPClient.Open "GET", TargetURL, False
HTTPClient.send

If HTTPClient.Status <> 200 Then
    MsgBox "Error downloading file: " & HTTPClient.Status & " - " & HTTPClient.statusText
    Exit Sub
End If

' ... rest of your code ...

Common Pitfalls & What to Check Next:

• Incorrect Spreadsheet ID: Double-check that the spreadsheet ID in your TargetURL is accurate. An incorrect ID will result in a 404 error.
• Network Connectivity: Ensure that your computer has a stable internet connection.
• Google Drive Permissions: Verify that your VBA script has the necessary permissions to access the Google Sheet. If you are using service account credentials, ensure the account has the correct access granted.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

[SwiftCoder15]

Your code’s downloading the Google Sheets web interface instead of the actual file. When you hit the /edit URL, you’re grabbing the HTML wrapper that shows the spreadsheet in your browser. Had the same issue last year when I was automating reports for my department. Those extra text bits and CSS errors? That’s leftover webpage junk. Switch to the export endpoint - change your URL to /export?format=xlsx&id=1abc123xyz789 instead of the edit link. Also throw in some error handling for the HTTP request status before writing to file. Google Drive sometimes sends back error pages that look like valid responses but are actually HTML error messages.