VBA QueryTable - Reset Error status?

I've seen other posts about this problem both on this forum and on other forums, but usually the responders aren't sure what the asker is asking... so I'll try to be clear.

I have a sheet that has a QueryTable embedded in it (to a web-based CSV file) that I update dynamically via VBA. Essentially, the macro changes the Connection string to a new address (based on a date), and then refreshes the link.

The problem is when I use a date too far in the future or for which a file has not been created, I get a "Run-time error '1004'" presumably because the URL produces a 404 error.

Unfortunately, even if I change the Connection string to a valid URL, I cannot rerun the macro unless I close the file and re-open it; however, I can refresh the querytable manually by right clicking on it.

Obviously, there must be some kind of ... I dunno, flag or something that gets set internally when the refresh fails, and the result is that the querytable won't refresh until the flag is cleared. I'm wondering if there is a way to clear this flag (if that's what it is) or if I have to find some kind of work-around -- in other words, maybe run a test to see if a given URL is valid or not, and not update the query table unless it is.

here's an example of the code I use (see attached sheet for working example):

Sub RefreshCSV()
    With Sheets("Sheet1").QueryTables("DataQuery")
        .Connection = "TEXT;http://www.iso-ne.com/histRpts/forecast-interchange/fcst_int_" & Format(Range("RUN_DATE"), "yyyymmdd") & ".csv"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

if "RUN_DATE" is today or earlier, this code will work fine. Try changing RUN_DATE to sometime in the future, and run the macro. It will give you an error because the file doesn't exist. But then try changing the date back to date that has past and try it again. It will give you the same error (basically it doesn't even try to refresh). How can I solve this? It's easily reproducible in Excel 2003 and 2007.

Any help appreciate, thanks!

Bumping in case anyone who missed it might be able to help...

Does nobody have any ideas about this?

I am having a similar problem. I am accessing a USA weather page to get wind information. Don't know much about querytables but the program I had last year worked great. They must of changed something because now I have to change the date every day where I didn't have to with the old address Here is the new URL
"URL;http://tidesandcurrents.noaa.gov/data_listing.shtml?bdate=20100819&edate=20100820&datum=0&unit=1&shift=d&stn=8539094%20Burlington,%20Delaware%20River,%20NJ&type=Meteorological%20Observations&format=View+Data&listing=1" _
, Destination:=Range("A5"))
Here is the URL where I didn't have to change the date every day
"URL;http://tidesonline.nos.noaa.gov/data_read.shtml?station_info=8539094+Burlington,+Delaware+River,+NJ+" _
, Destination:=Range("A1"))
Would appreciate any help on this would help

I have had the same problem and would benefit from a solution!