mysterious web querying problem

[platinumpimp68plus1]

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):

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!!

 

[platinumpimp68plus1]

nevermind, i figured it out by searching a little harder. turns out it wasn't my code at all, but a problem with IE7 caching.

if anyone is encountering the same problem, see this thread:

http://www.mrexcel.com/forum/showthread.php?t=295618&page=2

s7pete posts a nice solution.