How to download a excel file from Google Drive using vba?

Question

I am trying to download excel file from Google drive using vba code. The file gets downloaded in this path C:/MyDownloads/seriall.xlsx. But some weird text gets added on top of the downloaded excel file in the first sheet. And i also get a popup message with a message that the file you are trying to open is in a different format than specified. So I click yes to go through this popup and then I get a css file missing error popup. Why does this happen and why these errors appear in my downloaded excel file. My data is also shown at the bottom of weird text that excel adds on its on.

Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object

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

MyFile = "https://docs.google.com/spreadsheets/d/1e6DNpw3y5NrMR9cNLmIZdPYO79WLui7mua5I-5pEyKo/edit?usp=sharing"

WHTTP.Open "GET", MyFile, False
WHTTP.send
FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

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

FileNum = FreeFile
Open "C:\Downloads\serial.xls" For Binary As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "Open the folder [ C:\Downloads ] for the "

Answer 1

Using the 'GetSpecialFolder' UDF, you can download a file from any cloud drive, as simple as:

FileCopy GetSpecialFolder(vbDirGoogleDrive) & "seriall.xlsx", "C:/MyDownloads/seriall.xlsx"

http://www.EXCELGAARD.dk/Lib/GetSpecialFolder/

You can also use:

FileCopy GetSpecialFolder(vbDirGoogleDrive) & "seriall.xlsx", GetSpecialFolder(vbDirDownloads) & "seriall.xlsx"*

You can even move files between your, say, Google Drive and Dropbox:

FileCopy GetSpecialFolder(vbDirGoogleDrive) & "seriall.xlsx", GetSpecialFolder(vbDirDropbox) & "seriall.xlsx"*

Answer 2

Looking for Author

Option Compare Database
Public stTXT As String
'This line is to enable the 'Sleep' function which I use later.
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)

'To enable Excel.Application, Excel.Workbook and Excel.Worksheet - you need to enable
'the Excel objects in your Access file: in the VBA application go to 'Tools' menu > 
References.
'Find the Microsoft Excel 12.0 Object Library, and activate the checkbox.
'Now you have the full Excel library at your service.
'Here I used 'Object' - which is enough to make it work without the excel library.
    Dim appXL As Object 'Excel.Application
    Dim wbk As Object 'Excel.Workbook
    Dim wst As Object 'Excel.Worksheet
    Dim Timer As Integer

    Set appXL = CreateObject("Excel.Application")
'    appXL.Visible = True 'If you want to see the excel sheet - enable this row (good 
for debugging)
    Set wbk = appXL.Workbooks.Add
    Set wst = wbk.Worksheets(1)

    With wst
'In the following row, after the word 'key=' until the '&gid' - put the code-number 
of the google-doc spreadsheet, which you extract from the link you get for the 
spreadsheet google-doc (looks like: 'KeXnteS6n6...')

        .QueryTables.Add Connection:= _
            "URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=???&gid=1" _
            , Destination:=.Range("$A$1")
        .Name = "Worksheet1"
'The following fields are available if enabling Excel library (See above)
'        .FieldNames = True
'        .RowNumbers = False
'        .FillAdjacentFormulas = False
'        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
'        .BackgroundQuery = True
'        .RefreshStyle = xlInsertDeleteCells
'        .SavePassword = False
'        .SaveData = True
'        .AdjustColumnWidth = True
'        .RefreshPeriod = 0
'        .WebSelectionType = xlEntirePage
'        .WebFormatting = xlWebFormattingNone
'        .WebPreFormattedTextToColumns = True
'        .WebConsecutiveDelimitersAsOne = True
'        .WebSingleBlockTextImport = False
'        .WebDisableDateRecognition = False
'        .WebDisableRedirections = False
'        .Refresh BackgroundQuery:=False

        .QueryTables(1).Refresh
    End With

    'Wait for google-doc data to be downloaded.
    Timer = 0
    Do While Left(wst.Cells(1, 1), 12) = "ExternalData" And Timer < 40
        Sleep 250   ' Wait 0.25 sec before re-checking data
        Timer = Timer + 1
    Loop

    MsgBox "The value of cell AG2 is: " & wst.Cells(2, 34)

'Here you can work with the data...

'    wbk.Close SaveChanges:=False 'Don't save excel sheet
    wbk.Close SaveChanges:=True, FileName:="C:\Users\(User Name)\Desktop\GDocs" 'Save 
excel sheet in 'Documents' folder
    appXL.Quit

 'On Error GoTo ErrorHandler

  Exit Sub

Answer 3

Try below code,

Sub Basic_Web_Query() Dim chromePath As String Sheets("Sheet2").Select Range("A2").Select Selection.Copy Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe") SendKeys "^v" SendKeys "~" Application.Wait (Now + TimeValue("00:00:10")) SendKeys "^a" SendKeys "^c" Application.Wait (Now + TimeValue("00:00:10")) Sheets("Sheet1").Select Range("A1").Select ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False End Sub

---