[VBA] Runtime Error 3004 - Write to file failed

[Seba Robles]

Hello, so I have a code to download a google sheet as excel to my computer.

I'm getting the following error in my code and can't figure out what's wrong: (line highlighted in red)

Sub ImportGoogleSheet()

Dim URL, Location, FileName As String
Dim objWeb, objWrite As Object

URL = "Google Sheets URL Location"
Location = ThisWorkbook.Path & "\C:\Users\Username\Desktop\Exports"
FileName = "\GoogleSheet.csv"

'Connection to website
Set objWeb = CreateObject("MSXML2.XMLHTTP.3.0")

'Connecting to the website
objWeb.Open "Get", URL, False
objWeb.Send (URL)

If objWeb.Status = 200 Then

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

objWrite.Open
objWrite.Type = 1
objWrite.Write objWeb.ResponseBody
objWrite.Position = 0
objWrite.SaveToFile Location & FileName, 2
objWrite.Close
End If

End Sub

Thanks in advance!

 

[Norie]

Are you sure this returns a valid path?

Location = ThisWorkbook.Path & "\C:\Users\Username\Desktop\Exports"

 

[Seba Robles]

Yes, I copied it directly from windows explorer..

Location = ThisWorkbook.Path & "C:\Users\Username\OneDrive\Desktop\IISE\GS Exports"

That's the full path

 

[Norie]

Let's say your workbook is saved in "C:\Test", if that was the case then this would be the value of Location.

C:\TestC:\Users\Username\OneDrive\Desktop\IISE\GS Exports"

To me that doesn't look like valid path.

 

[Seba Robles]

Ok I'm not really following

The path where the workbook where I'll run the macro is saved here: C:\Users\username\OneDrive\Desktop\IISE

The path where I want to save the google sheet export is: C:\Users\username\OneDrive\Desktop\IISE\GS Exports

So the location path should be "C:\Users\username\OneDrive\Desktop\IISEC:\Users\srobl\OneDrive\Desktop\IISE\GS Exports" ?

 

[Norie]

Try this.

Location = ThisWorkbook.Path & Application.PathSeparator & "GS Exports\"

 

[Seba Robles]

No luck, I'm still getting the run time error 3004

 

[Norie]

Do you have a folder named 'GS Exports' in the same folder as the workbook you are running the code from?

 

[Seba Robles]

I just hard coded the location to the desktop and saved it there and it runs.. I will have to change it later to save it on the desktop regardless of the user running the workbook.

Thanks!