VBA script to fetch Excel file from Google Drive - unexpected errors?

Other Questions Google drive
1

I’m having trouble with a VBA script that’s supposed to grab an Excel file from Google Drive. It’s saving to C:/MyDownloads/seriall.xlsx, but there are some weird issues:

  1. Random text shows up at the top of the first sheet
  2. A popup says the file format is different than expected
  3. After clicking through that, I get a CSS file missing error

My data is there, but it’s below all this strange text. Here’s a simplified version of my code:

Sub FetchGoogleDriveFile()
  Dim netRequest As Object
  Dim fileContent() As Byte
  Dim targetPath As String
  
  Set netRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
  
  netRequest.Open "GET", "https://drive.google.com/uc?export=download&id=YOUR_FILE_ID", False
  netRequest.Send
  
  fileContent = netRequest.ResponseBody
  
  targetPath = "C:\MyDownloads\report.xlsx"
  
  Open targetPath For Binary Access Write As #1
    Put #1, , fileContent
  Close #1
  
  MsgBox "File downloaded to " & targetPath
End Sub

Any ideas why this is happening or how to fix it? Thanks!

2

I’ve dealt with similar headaches when trying to fetch files from Google Drive. One thing that worked for me was using the ‘confirm=t’ parameter in the URL. Try modifying your URL like this:

netRequest.Open “GET”, “https://drive.google.com/uc?export=download&id=YOUR_FILE_ID&confirm=t”, False

This parameter tells Google you’re confirming the download, which often bypasses that HTML response. Also, make sure you’re using the correct file ID - sometimes that can cause unexpected results.

If that doesn’t solve it, you might want to look into using Google’s official Drive API as lucasg suggested. It’s a bit more work upfront, but it’s way more reliable in the long run. Good luck!

3

I encountered a similar issue when working with Google Drive API. The problem likely stems from how Google handles direct download links. Instead of using WinHttp, I’d suggest leveraging the Google Drive API with OAuth2 authentication. This approach is more reliable and secure.

Here’s a high-level outline of the steps:

  1. Set up a Google Cloud project
  2. Enable the Drive API
  3. Create OAuth2 credentials
  4. Use those credentials in your VBA code

Implementing this solution requires more initial setup, but it resolves the issues you’re facing and provides a more robust long-term solution. If you need help with the implementation, I can provide more detailed guidance.

4

hey Nova56, i’ve seen this before. sounds like google is returning html instead of the actual file. try adding a user agent header to your request:

netRequest.SetRequestHeader “User-Agent”, “Mozilla/5.0”

that might trick google into giving you the xlsx. let me know if it works!