platinumpimp68plus1
New Member
- Joined
- Jul 27, 2010
- Messages
- 2
I'm trying to write a macro that dynamically web queries google analytics data. It was working perfectly fine last week, but suddenly it gives me the "Run-time error '1004': The file could not be accessed. Try one of the following: Make sure the specified folder exists. Make sure the folder that contains the file is not read-only. Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *. Make sure the file/path doesn't contain more than 218" and the debugger highlights "Refresh BackgroundQuery:=False"
But heres the strange thing: it works every other time that I run it... ?
I thought maybe the bad characters in the URL were causing the problem, so I escaped them, but the problem still prevails.
Here is my code (note I altered the url a bit):
Its driving me crazy, so any help is greatly appreciated!!
But heres the strange thing: it works every other time that I run it... ?
I thought maybe the bad characters in the URL were causing the problem, so I escaped them, but the problem still prevails.
Here is my code (note I altered the url a bit):
Code:
Sub GoogleAnalytics()
Dim date0, date1, date2, date3, LastRow, LastMonth, LastYear, MYURL As String
Range("A5:G40").Delete
' Clears old data
LastMonth = DateAdd("m", -1, Date)
LastYear = DateAdd("yyyy", -1, Date)
date0 = Format(Date, "yyyymmdd")
date1 = Format(DateAdd("d", -7, Date), "yyyymmdd")
date2 = Format(DateAdd("d", -7, LastMonth), "yyyymmdd")
date3 = Format(DateAdd("m", -1, Date), "yyyymmdd")
' Dynamic date ranges for the data I want to extract
MYURL = "https://www.google.com/analytics/reporting/custom" & date1 & "-" & date0 & "moreurl" & date2 & "-" & date3 & "endofurl"
Set qt = ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MYURL _
, Destination:=Range("B5"))
With qt
.RefreshPeriod = 0
.BackgroundQuery = True
.SaveData = True
.AdjustColumnWidth = False
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """f_table_data"""
.Refresh BackgroundQuery:=False
End With
End Sub
Its driving me crazy, so any help is greatly appreciated!!