VBA script for fetching Excel file from Google Drive causing formatting issues?

Other Questions Google drive
1

I’m having trouble with a VBA script that downloads an Excel file from Google Drive. The file saves to C:/MyDownloads/seriall.xlsx, but there are some problems:

  1. Strange text appears at the top of the first sheet
  2. A popup warns about a format mismatch
  3. I get a CSS file missing error

My data shows up below the weird text. Why is this happening? Here’s a simplified version of my code:

Sub DownloadGoogleDriveFile()
    Dim webClient As Object
    Dim fileData() As Byte
    Dim filePath As String

    Set webClient = CreateObject("MSXML2.XMLHTTP")
    filePath = "C:\MyDownloads\report.xlsx"

    webClient.Open "GET", "https://drive.google.com/uc?export=download&id=EXAMPLE_FILE_ID", False
    webClient.send

    If webClient.Status = 200 Then
        fileData = webClient.responseBody
        
        Open filePath For Binary Access Write As #1
        Put #1, , fileData
        Close #1
        
        MsgBox "File downloaded successfully!"
    Else
        MsgBox "Download failed. Status: " & webClient.Status
    End If

    Set webClient = Nothing
End Sub

Any ideas on how to fix these issues?

2

I’ve encountered similar issues when downloading Excel files from Google Drive using VBA. The problem likely stems from how Google Drive handles file exports.

Here’s what I found works:

Instead of using MSXML2.XMLHTTP, try using WinHttp.WinHttpRequest.5.1. This object seems to handle Google Drive’s response better.

Also, you need to follow redirects. Google Drive often sends a redirect before the actual file download. Add a loop to follow these redirects until you get the final file.

Lastly, check the Content-Type header. If it’s not ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’, you’re probably getting an HTML error page instead of the Excel file.

These changes should resolve the formatting issues and eliminate the strange text and popups. If you’re still having trouble, double-check your file ID and make sure you have the necessary permissions to access the file on Google Drive.

3

hey there! i’ve had this issue too. it may be because google drive sends html before the file. try using WinHttp.WinHttpRequest.5.1 over MSXML2.XMLHTTP and follow redirects. check the content type header; it should indicate an excel file.

4

I’ve dealt with this issue before. The problem lies in how Google Drive serves files. Your script is likely downloading the webpage instead of the actual Excel file. To fix this, you need to handle redirects and ensure you’re getting the correct content type.

Try using the WinHttp.WinHttpRequest.5.1 object instead of MSXML2.XMLHTTP. It handles redirects better. Also, add a loop to follow redirects until you get the final file. Check the Content-Type header to confirm you’re receiving an Excel file.

Another tip: Use the ‘export=download’ parameter in your URL. This forces Google Drive to serve the file directly rather than showing a preview page.

If issues persist, verify your file ID and ensure you have the necessary permissions to access the file on Google Drive.