Download Excel file from Google drive

Try this

Option Explicit
Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
    ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub DownloadAndOpenTempFile()
    Dim fileURL As String
    Dim tempPath As String
    Dim savePath As String

    ' Get the temporary folder path
    tempPath = Environ("TEMP")
    savePath = tempPath & "\YourFile.xlsx" ' Define the file name in temp
     'Tip: right click on the file in Google Drive, choose share then copy link
    fileURL = "https://drive.google.com/uc?export=download&id=FILE_ID" ' Replace FILE_ID with actual file ID

    ' Download the file to temp
    URLDownloadToFile 0, fileURL, savePath, 0, 0

    ' Open the file
    Workbooks.Open savePath
End Sub

When you download a file using a direct URL from Google Drive, the saved file name is determined by what you specify in your VBA code, not automatically by the original name in Google Drive.

However, if you want to maintain the original file name, you'd need to retrieve it from Google Drive before downloading. This would usually require using the Google Drive API with authentication, since Google Drive doesn't automatically append file names to download links.

For simplicity, you could manually specify the file name in the savePath variable  as in the provided code. If you're looking for an automated approach to get the exact file name dynamically, you'd need to authenticate and retrieve file metadata via the API.