Wen Query Issue

H

Hans

When do Web Query in Excel, got the problem with “the file could not be
accessedâ€, What is this problem? How to solve it?
 
H

Hans

Thanks Joel

Yes. it sometimes works, but when I repeat web query more times it dose not
work. After I delete the temporary internet files, it works again, but this
does not sovle my problem, since my web qurey will run many times, in which
the code was wrotteb by VBA in excel. Is there a way to delete temporary
internet files from VBA code when each time I start to run web query?

Minghui
 
J

Joel

Temporary Internet files are stored in the following directory on my PC. You
can find the exact location by going into options on Internt explorer where
you delete temporary internet files and view files. To see these files you
must change you window explorer settings to view possible system files.

C:\Documents and Settings\Joel\Local Settings\Temporary Internet Files

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile "c:\temp\abc.txt"


to delete all files in a directory

Folder = "C:\Documents and Settings\Joel\Local Settings\Temporary Internet
Files\"
Set fs = CreateObject("Scripting.FileSystemObject")

FName = Dir(Folder & "*.*")
Do while FName <> ""
fs.DeleteFile Folder & FName
FName = Dir(
loop
 
H

Hans

Hi Joel

Thanks for your help. The code is great, but it does not help me to delete
all htm files from Temporary Internet Files. When run the program with the
code you suggested, it cannot find any file to delete. In fact, the files
are under sub directory of Content.IE5 which there are some random sub
folders names to store htm files, If I set path with C:\Documents and
Settings\userid\Local Settings\Temporary Internet Files\Content.IE5\ , we can
only find one file: index.dat, this file is not allowed to delete. How can I
delete those htm files under sub directories?

Thanks for your help

Hans
 
J

Joel

Try this code. It is a recursive algorithm that will search all sub folders.

Sub GetWorksheets()

strFolder = "C:\Documents and Settings\Joel\Local Settings\Temporary
Internet Files"

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set Folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\")
End Sub

Sub GetWorksheetsSubFolder(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set Folder = _
fso.GetFolder(strFolder)

If Folder.subfolders.Count > 0 Then
For Each sf In Folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
For Each fl In Folder.Files
If Right(UCase(fl.Name), 4) = ".HTM" Then
fso.DeleteFile strFolder & fl.Name
End If
Next fl

200 On Error GoTo 0

End Sub
 
H

Hans

Thanks Joel

Your new code is work for my case after I add line

On Error Resume Next

into For Each condition code

i.e.

For Each fl In Folder.Files

If Right(UCase(fl.Name), 4) = ".HTM" Then
On Error Resume Next
fso.DeleteFile strFolder & fl.Name
End If
Next fl

If I do not add this line, when find any htm file which is being using by
another person or program, the program will stop For Each condition. and it
goes to next sub folder, i.e. the rest of htm files in current sub folder
will be not deleted.

Thanks again for your help. You sovle my problem

Hans
 
Top