1

I am trying to create a VBA to download a google sheet into excel so I can compile stock market data daily. I would simply use power query for this but I am doing this on my personal laptop which is a mac and does not support power query. I am relatively new to coding so have been leaning on following online instructions. The instruction includes this:

Set objWebCon = CreateObject("MSXML2.XMLHTTP.3.0")

This line when ran creates an error message saying:

" Run-time error '429': ActiveX component can't create object "

I think the issue lies within the fact that the instruction is based on a windows operating system. Any solution I've searched for is specific to windows operating systems.

Does anybody here know if I can change the "MSXML2.XMLHTTP.3.0" part of my code to fit it better to mac? Not sure if this is what needs to be done but any guidance would be super appreciated.

I attached my full code below but feel free to ignore it if not relavent. Thank you!!


Sub DownloadGoogleSheets()

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

'Sheet Url
ShtUrl = "https://docs.google.com/spreadsheets/d/1wpA_epxtlz96sxETqKttJwsy9Aubb15H8xslcSQ20T0/export?format=csv&id=1wpA_epxtlz96sxETqKttJwsy9Aubb15H8xslcSQ20T0" & gid = 1319327791

'Location
Location = ThisWorkbook.Path & "\"  '/Users/[myName]/Desktop/Stock Analysis/n"

'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
braX
  • 11,506
  • 5
  • 20
  • 33
Frascy
  • 11
  • 2
  • [Seems to be a similar question](https://stackoverflow.com/questions/52628834/web-services-in-vba-using-msxml-serverxmlhttp-on-mac). – BigBen May 13 '20 at 18:35
  • If you run step by step (from the Debug menu), does the error occur on `CreateObject("MSXML2.XMLHTTP.3.0")`? – dekingsey May 13 '20 at 23:30
  • Also, just noticed that, on the line that starts with the `ShtUrl = ...`, the gid parameter is outside the quotes. – dekingsey May 13 '20 at 23:32

0 Answers0