Web QueryTable of DOOOOOOOM!!!!

[BlueSocksMcGee]

Hi, I'm at hair pulling stage:
I'm constructing relativly simple macro to pull web query for each row (depending on row data http changes) which looks at an INTRAnet site, and depending on the output marks a cell true or false. I'm getting an error which is driving me crazy!!!!
The real annoying part is that it works for some rows, but then randomly throws an error for others. I manualy checked and webpages exist for them all. Also when I try to manualy run a web query from regular interface I get the same error, which didn't happen earlier today but now 100% of the time!!!! I don't get it.
Bottom line I have a legitimate web site (wish I could share the link it's intranet) that when I try to web query I get this error. Anyone know whats going on?

Here is the 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 name doesn't contain more than 218 characters."

My code

For Each CELL In Selection
SERIAL = Right(CELL.Offset(0, -5).Range("A1").FormulaR1C1, 6) + Left(CELL.Offset(0, -5).Range("A1").FormulaR1C1, 3)

'web query which appends SERIAL (6 digit variable) to end of URL
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://bluepages.ibm.com/BpHttpApis/wsapi?byCnum=" & SERIAL _
        , Destination:=Range("Z1")) 'puts table in Column Z row 1
        .Name = _
        "Is Person There?" & SERIAL
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    'check returned data
    If Range("Z2").FormulaR1C1 <> "//0" Then 'checks second row of resulting table
        CELL.FormulaR1C1 = True
    Else: CELL.FormulaR1C1 = False
    End If

[BlueSocksMcGee]

Anyone? please? *tearing up*

[BlueSocksMcGee]

Turns out this is a really strange bug that relates to temp web pages. Basically your comp saves the web query as a temp htm file in the temporary internet files folder. When it has too many files in there the file names go crazy and cause the error. Manualy deleting the files in there sets you up for a while. Can't seem to automate the deletion of the files... here is a link to where I found the solution:

http://techrepublic.com.com/5208-623...sageID=2301201

http://www.vbforums.com/showthread.php?t=453684

[BlueSocksMcGee]

Now I know what the bug is for running many web queries close to eachother (that excel creates .htm files in Temp Internet Folder and too many causes naming convention issue). But I having a difficult time coming up with a programatical solution to it.

Manualy I delete all the .htm files in my Temp Internet Folder, when the bug occures and am able to continue running my macro until the bug occures again and then delete the files etc.

I tried builing in a Kill statement to delete *.htm files in the folder, but that does not seem to work. I believe Temp Internet Files is a special cache folder which may not even exist in windows folder tree.... Can anyone design a macro to delete temp cached .htm files from their computer? I've tried to no avail for the past couple of days. I CANNOT completly clean out my cache because I'd loose all my cookies and passwords etc and I need these. I only want to delete .htm or specifically the files the web queries creates. Alternitavly if I can force a name for the files the web query creates, or reroute them to a different location, then I could apply the Kill statement to them.
My folder sits at:
C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files

[brouz]

Did you ever find a solution for this? I have the same issue. All I really want to do is Kill files in the temporary internet files folder.

as you stated, this doesn't work:

Kill "C:\Documents and Settings\" & Environ("username") & "\Local Settings\Temporary Internet Files\*.htm"

this does not work either:

Kill "C:\Documents and Settings\" & Environ("username") & "\Local Settings\Temporary Internet Files\*.*"

[BlueSocksMcGee]

No I still havn't! It's really a painfull bug... I still manualy delete files it takes forever.

Originally Posted by brouz

Did you ever find a solution for this? I have the same issue. All I really want to do is Kill files in the temporary internet files folder.

as you stated, this doesn't work:

Kill "C:\Documents and Settings\" & Environ("username") & "\Local Settings\Temporary Internet Files\*.htm"

this does not work either:

Kill "C:\Documents and Settings\" & Environ("username") & "\Local Settings\Temporary Internet Files\*.*"

[brouz]

I'm trying out some code here which is supposed to "Delete the IE Cache"

http://vbnet.mvps.org/index.html?cod...teurlcache.htm

I'll let you know how it goes!

[jkelly92122]

I was having the same problem with code i had written to return 60,000+ web queries and was getting tired of deleting temporary internet files after every 50 queries. Below is a rather odd work around i came up with:

1) Create a desktop shortcut that clears the Temporary Internet Files folder as follows:
- Right click on desktop, select New then Shortcut
- for the location type:
RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8

Right click on the shortcut and make ctrl + Alt + d the short cut

Below is my sub that calls the my function that queries the web:
MyNum = 0
Dim myRange As Range
Set myRange = Worksheets(1).Range("D2:D65256") '65256
For Each C In myRange ' Iterate through each element
C.Select 'Select each cell
Run (Retrieve)
MyNum = MyNum + 1
If MyNum = 30 Then
MyNum = 1
SendKeys "^%(d)"
End If
Next

After every 30 iterations, it uses the SendKey method to clear my Temporary Internet Files Folder.

Hope this helps,
JK.

[brouz]

thanks, i will try that out... in the meantime, i had been using this macro to open the temp int folder, then just manually clearing them:

Sub Open_Explorer()
Shell "Explorer.exe /n,/e,C:\Documents and Settings\" & Environ("username") & "\Local Settings\Temporary Internet Files", vbMaximizedFocus
End Sub

[BlueSocksMcGee]

Excellent! Finally an automated solution!
There is the issue though that this will delete ALL my temp internet files (including stored passwords and other usefull cookies). I'm gonna play with this on a dummy machine and see if I can come up with a focused version that only removes the specific web query files created by excel. Failing that, this is way better than the manual process! Thanks jkelly92122!