Wednesday 15 November 2017

Excel VBA: How to Download Google Sheet

Downloading Google Sheets is easy as a click.

Sub DownloadGoogleSheets()

Dim ShtUrl, Location, FileName As String
Dim objWebCon, objWrit As Object

'Sheet Url
ShtUrl = "https://docs.google.com/spreadsheets/d/1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY/export?format=csv&id=1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY&gid=0"  'Need to replace id and gid

'Location
Location = ThisWorkbook.Path & "\"  'C:\Export\" Replace with location

'FileName
FileName = "GoogleSheet.csv"

'Connection to Website
Set objWebCon = CreateObject("MSXML2.XMLHTTP.3.0")

'Writer
Set objWrit = CreateObject("ADODB.Stream")

'Connecting to the Website
objWebCon.Open "Get", ShtUrl, False
objWebCon.Send (ShtUrl)

'Once page is fully loaded
If objWebCon.Status = 200 Then

 'Write the text of the sheet
objWrit.Open
objWrit.Type = 1
objWrit.Write objWebCon.ResponseBody
objWrit.Position = 0
objWrit.SaveToFile Location & FileName
objWrit.Close

End If

Set objWebCon = Nothing
Set objWrit = Nothing

End Sub

See it in Action

Posted by at
Labels: , ,

43 comments:

  1. Unknown24 March 2018 at 11:16

    Excellent code, Shakti. Thanks for sharing. I was able to run in from Access by setting Location to a literal, and to download in Excel format simply by changing "format=csv" to "format=xlsx". This took a bit of trial and error since I couldn't find a published list of formats. Fortunately, "csv" vs "xlsx" is fairly intuitive.

    ReplyDelete
  2. Unknown15 April 2018 at 02:13

    great code. i have a problem: the information in the csv file is mostly gibrish because my data is in hebrew. any suggestions how to copy the data and keep the hebrew?:)

    ReplyDelete
    Replies
    1. Unknown1 April 2019 at 03:10

      Shalom Gever,
      for Hebrew and any languages, you need specify UTF-8 format

      objWrit.Open
      objWrit.Charset = "utf-8"

      Delete
  3. Kamal Bharakhda 21 April 2018 at 08:04

    Thank you for this help.

    My question is, I want to download that file as .xls or .xlsx format

    Please help me with that.

    ReplyDelete
  4. Unknown21 April 2018 at 08:22

    This comment has been removed by the author.

    ReplyDelete
  5. Unknown21 April 2018 at 08:23

    es a great piece of code but I have one question.

    Rather than creating a separate file, how can I write the data to a SS that has the VB code?

    ReplyDelete
  6. Unknown10 May 2018 at 08:29

    Hi, works great thank you. What about the other way around ?

    ReplyDelete
  7. Unknown2 June 2018 at 14:57

    I am a Typical student and love to work in excel for my Various works. Thanks to post your blog.

    ReplyDelete
  8. King Kong8 September 2018 at 20:44

    It worked! thanks so much <3

    ReplyDelete
  9. Unknown13 September 2018 at 11:14

    Hello!
    This is great code, but I get the runtime 429 error when it tries to CreateObject("MSXML2.XMLHTTP.3.0")

    Any suggestions?

    ReplyDelete
  10. Anonymous25 October 2018 at 22:25

    hi i got the error of access is denied

    ReplyDelete
  11. Anonymous25 October 2018 at 22:31

    hiiii

    i got an error of access denied please let me

    ReplyDelete
  12. Unknown13 November 2018 at 12:31

    Hi Shakti,
    Nice Work..!!
    Can you explain how to upload Files in Google Drive.?

    ReplyDelete
  13. Shanki3 December 2018 at 23:45

    Hi sakthi,
    i am getting the following error, how to resolve it
    activex component can't create object in vb6 while creating MSXMS2.XMLHTTP.3.0

    ReplyDelete
  14. Anonymous4 May 2019 at 23:13

    Hey, can anyone tell how to download multiple tabs in a singles google sheets using VBA

    ReplyDelete
  15. Unknown15 May 2019 at 05:31

    I got also access denied. Can anyone help me with that? Thanks in advance

    ReplyDelete
  16. Surendra29 June 2019 at 05:03

    i am getting the following error
    this line

    objWebCon.Send (ShtUrl)

    ReplyDelete
  17. Anonymous27 July 2019 at 09:57

    Shakti, Great work but, I am stucking

    Something error is here,

    objWebCon.Open "Get", ShtUrl, False



    objWebCon.Send (ShtUrl)


    Please help me out

    ReplyDelete
    Replies
    1. Harshit1 October 2019 at 09:03

      Am also facing same issue.

      Delete
    2. Unknown24 November 2019 at 22:47

      Am also facing same issue.

      Something error is here,

      objWebCon.Open "Get", ShtUrl, False

      Delete
  18. analystera6 August 2019 at 04:08

    Your blog is too good and informative i need this information thanks for sharing.
    online training

    ReplyDelete
  19. Unknown14 January 2020 at 00:39

    Something error is here,

    objWebCon.Send (ShtUrl)

    >> Run-time error'-2147024891(80070005)' Access is denied

    Please help me out

    ReplyDelete
    Replies
    1. matahari18 February 2020 at 23:28

      use shareable unik id, insya Alloh working

      Delete
  20. Guru23 January 2020 at 04:05

    Hi Shakti, using the same code that you provided, whenever I re-run the macro, the output file should be replaced with the existing one. Because every time I need a refreshed output file, I delete the existing one and then re-run the macro.

    Tried using Application.DiplayAlerts=False/True, but it doesn't work.

    Please advise.

    ReplyDelete
  21. wordpar2912 March 2020 at 02:34

    Thanks for your great information, the contents are quiet interesting.Keep updating more information from your blog.I will be waiting for your next post
    Translation Company In India | Indian Languages Translator | Translation Company In Noida | Certified Translation Services In Pune | Voice-over Services | E-learning Localization

    ReplyDelete
  22. Unknown25 April 2020 at 21:04

    Shakti how can i contact you? do you do consulting work?

    ReplyDelete
  23. JD1 July 2020 at 00:30

    Thanks so much for this. I had this work well for a month or so but then on 25Jun20 about midday in SE Asia it started giving the Access is denied error described at 7:08 in the YouTube video. I dont recall anything changing. I checked my share settings as well as web publishing settings. Anyone have any idea of why this error occurs? NOTE: I did try making a copy of the sheet and changing the share to fit but it fails too. Perhaps Google made some kind of changes somewhere? I'm using 64 bit MS Office Professional Plus 2016 BTW

    ReplyDelete
  24. Hamza Almas2 July 2020 at 08:42

    Thanks for sharing this helpful information. Its really work.
    MS Excel Course

    ReplyDelete
  25. Unknown2 July 2020 at 20:30

    JD, i had dame problema in a similar macro to download Google spreadsheet i have used for 2 years, i m looking to know what happened

    ReplyDelete
  26. hb3 July 2020 at 09:04

    May have happened after a Microsoft update?

    ReplyDelete
  27. Unknown3 August 2020 at 21:22

    Something error is here,

    objWebCon.Send (ShtUrl)

    >> Run-time error'-2147024891(80070005)' Access is denied

    Already share google sheet. Please help me.

    ReplyDelete
    Replies
    1. Unknown4 August 2020 at 09:21

      I have the same error, the code worked perfectly up until 2 day ago. Now it shows the same error. Pls help

      Delete
    2. Unknown5 August 2020 at 22:28

      I can solve this error change shturl from (
      ShtUrl = "https://docs.google.com/spreadsheets/d/1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY/export?format=csv&id=1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY&gid=0" ) to (ShtUrl = "https://docs.google.com/spreadsheets/d/1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY/gviz/tq?tqx=out:csv") . It's work.

      Delete
    3. Joan25 August 2020 at 01:54

      This comment has been removed by the author.

      Delete
    4. Joan25 August 2020 at 01:55

      This comment has been removed by the author.

      Delete
    5. Joan25 August 2020 at 01:58

      For CSV export data, the direct link "https://docs.google.com/spreadsheets/d/1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY/gviz/tq?

      tqx=out:csv") it works fine (Many thanks!). But if you want export to XLX it doesn't work correctly.

      My last link was ShtUrl = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/export?format=xlsx&id=xxxxxxxxxxxx&gid=xxxx", I

      tried to change to this link "https://docs.google.com/spreadsheets/d/xxxxxxxxxxx/gviz/tq?tqx=out:xlsx" in order to export to excel

      but it not works correctly. I can download the file, but excel find the file corrupted.
      Does anyone have the solution for direct download to XLSX?
      Many thanks,

      Delete
    6. Unknown13 November 2020 at 00:56

      Keep:
      ShtUrl = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/export?format=xlsx&id=xxxxxxxxxxxx&gid=xxxx"

      And change:
      Set objWebCon = CreateObject("MSXML2.XMLHTTP.3.0")
      to:
      Set objWebCon = CreateObject("MSXML2.ServerXMLHTTP.3.0")

      That works for me, if I want to save it as .xlsx

      Delete
    7. Joan23 November 2020 at 00:34

      Many thanks Unknown.
      Only change Set objWebCon = CreateObject("MSXML2.XMLHTTP.3.0")to Set objWebCon = CreateObject("MSXML2.ServerXMLHTTP.3.0")and it works fine. Thanks!

      Delete
    8. Aruna16 October 2022 at 12:17

      Thanks.
      CreateObject("MSXML2.XMLHTTP.3.0")to Set objWebCon = CreateObject("MSXML2.ServerXMLHTTP.3.0")
      Works for me ..!


      Delete
  28. Alfred20 January 2021 at 22:57

    Nice blog! Blog is very useful for everyone. Trunao provides convert your excel spreadsheets to a web app and access your online database anytime, anywhere.

    ReplyDelete
  29. Unknown10 October 2021 at 14:31

    Is there a way to incorporate the login and password into this code to download a non-public GSheet?

    ReplyDelete
  30. Unknown22 October 2021 at 00:30

    when i am running the macro it is saving it as a "file" and i cannot use. Please help

    ReplyDelete
  31. Bell22 December 2021 at 04:12

    This is awesome, thanks Shakti!

    ReplyDelete

Subscribe to: Post Comments (Atom)