mysterious web querying problem

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

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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,534
Messages
5,511,879
Members
408,867
Latest member
Ranjeet yamgekar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top