The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2010, 12:52 PM
PokerAKQJT PokerAKQJT is offline
Guest
 
Join Date: Dec 2009
Clear Internet Explorer Cache in VBA

What VBA code will clear Internet Explorer temp files, cookies,and history from an Excel VBA macro. Thank you.
Reply With Quote
Advertisements  
  #2  
Old 01-10-2010, 10:41 AM
Ellis Dee Ellis Dee is offline
Guest
 
Join Date: Jul 2003
Here's VB6 code, which generally works fine in VBA.
Code:
Option Explicit

' Constants for API
Private Const ERROR_CACHE_FIND_FAIL As Long = 0
Private Const ERROR_CACHE_FIND_SUCCESS As Long = 1
Private Const ERROR_FILE_NOT_FOUND As Long = 2
Private Const ERROR_ACCESS_DENIED As Long = 5
Private Const ERROR_INSUFFICIENT_BUFFER As Long = 122
Private Const MAX_CACHE_ENTRY_INFO_SIZE As Long = 4096

Private Const LMEM_FIXED As Long = &H0
Private Const LMEM_ZEROINIT As Long = &H40
Private Const LPTR As Long = (LMEM_FIXED Or LMEM_ZEROINIT)

Private Const NORMAL_CACHE_ENTRY As Long = &H1
Private Const EDITED_CACHE_ENTRY As Long = &H8
Private Const TRACK_OFFLINE_CACHE_ENTRY As Long = &H10
Private Const TRACK_ONLINE_CACHE_ENTRY As Long = &H20
Private Const STICKY_CACHE_ENTRY As Long = &H40
Private Const SPARSE_CACHE_ENTRY As Long = &H10000
Private Const COOKIE_CACHE_ENTRY As Long = &H100000
Private Const URLHISTORY_CACHE_ENTRY As Long = &H200000
Private Const URLCACHE_FIND_DEFAULT_FILTER As Long = _
    NORMAL_CACHE_ENTRY Or _
    COOKIE_CACHE_ENTRY Or _
    URLHISTORY_CACHE_ENTRY Or _
    TRACK_OFFLINE_CACHE_ENTRY Or _
    TRACK_ONLINE_CACHE_ENTRY Or _
    STICKY_CACHE_ENTRY

Private Const CLSID_CUrlHistory = "{3C374A40-BAE4-11CF-BF7D-00AA006946EE}"
Private Const CLSID_IUrlHistoryStg2 = "{AFA0DC11-C313-11D0-831A-00C04FD5AE38}"
Private Const IUrlHistoryStg2_Release As Long = 8&
Private Const IUrlHistoryStg2_ClearHistory As Long = 36&
Private Const CLSCTX_INPROC_SERVER As Long = 1&
Private Const CC_STDCALL As Long = 4&
Private Const S_OK As Long = 0&

Private Type GUID
   Data1 As Long
   Data2 As Integer
   Data3 As Integer
   Data4(7) As Byte
End Type

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Type INTERNET_CACHE_ENTRY_INFO
    dwStructSize As Long
    lpszSourceUrlName As Long
    lpszLocalFileName As Long
    CacheEntryType  As Long
    dwUseCount As Long
    dwHitRate As Long
    dwSizeLow As Long
    dwSizeHigh As Long
    LastModifiedTime As FILETIME
    ExpireTime As FILETIME
    LastAccessTime As FILETIME
    LastSyncTime As FILETIME
    lpHeaderInfo As Long
    dwHeaderInfoSize As Long
    lpszFileExtension As Long
    dwExemptDelta As Long
End Type

' API declarations for clearing browser cache
Private Declare Function CLSIDFromString Lib "ole32" (ByVal lpszGuid As Long, pGuid As Any) As Long
Private Declare Function CoCreateInstance Lib "ole32" (rclsid As Any, ByVal pUnkOuter As Long, ByVal dwClsContext As Long, riid As Any, pvarResult As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSource As Any, ByVal dwLength As Long)
Private Declare Function DeleteUrlCacheEntry Lib "wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
Private Declare Function DispCallFunc Lib "oleaut32" (ByVal pvarResult As Long, ByVal oVft As Long, ByVal cc As Long, ByVal vtReturn As VbVarType, ByVal nParams As Long, pVarTypes As Long, pVarArgs As Long, pvarResult As Variant) As Long
Private Declare Function FindCloseUrlCache Lib "wininet" (ByVal hEnumHandle As Long) As Long
Private Declare Function FindFirstUrlCacheEntry Lib "wininet.dll" Alias "FindFirstUrlCacheEntryA" (ByVal lpszUrlSearchPattern As String, ByRef lpFirstCacheEntryInfo As Any, ByRef lpdwFirstCacheEntryInfoBufferSize As Long) As Long
Private Declare Function FindNextUrlCacheEntry Lib "wininet.dll" Alias "FindNextUrlCacheEntryA" (ByVal hEnumHandle As Long, ByRef lpNextCacheEntryInfo As Any, ByRef lpdwNextCacheEntryInfoBufferSize As Long) As Long
Private Declare Function LocalAlloc Lib "kernel32" (ByVal uFlags As Long, ByVal uBytes As Long) As Long
Private Declare Function LocalFree Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpyA Lib "kernel32" (ByVal RetVal As String, ByVal Ptr As Long) As Long
Private Declare Function lstrlenA Lib "kernel32" (ByVal Ptr As Any) As Long

Public Sub ClearCache()
    Dim ICEI As INTERNET_CACHE_ENTRY_INFO
    Dim hFile As Long
    Dim cachefile As String
    Dim nCount As Long
    Dim dwBuffer As Long
    Dim pntrICE As Long
    Const COOKIE_CACHE_ENTRY As Long = &H100000
    Dim strCookie As String
    
    dwBuffer = 0
    'Get the required buffer size
    hFile = FindFirstUrlCacheEntry(vbNullString, ByVal 0, dwBuffer)
    'both conditions should be met by the first call
    If (hFile = ERROR_CACHE_FIND_FAIL) And _
     (Err.LastDllError = ERROR_INSUFFICIENT_BUFFER) Then

        'The INTERNET_CACHE_ENTRY_INFO data type is a
        'variable-length type. It is necessary to allocate
        'memory for the result of the call and pass the
        'pointer to this memory location to the API.
        pntrICE = LocalAlloc(LMEM_FIXED, dwBuffer)
        'allocation successful
        If pntrICE Then
            'set a Long pointer to the memory location
            CopyMemory ByVal pntrICE, dwBuffer, 4
            'and call the first find API again passing the
            'pointer to the allocated memory
            hFile = FindFirstUrlCacheEntry(vbNullString, ByVal pntrICE, dwBuffer)
            'hfile should = 1 (success)
            If hFile <> ERROR_CACHE_FIND_FAIL Then
                'now just loop through the cache
                Do
                    'the pointer has been filled, so move the
                    'data back into a ICEI structure
                    CopyMemory ICEI, ByVal pntrICE, Len(ICEI)
                    
                    'CacheEntryType is a long representing the type of
                    'entry returned.
                    blnProtected = False
                    If (ICEI.CacheEntryType And COOKIE_CACHE_ENTRY) Then
                        strCookie = GetStrFromPtr(ICEI.lpszSourceUrlName)
                        strCookie = Mid$(strCookie, InStr(strCookie, "@") + 1)
                    End If
                    DeleteUrlCacheEntry GetStrFromPtr(ICEI.lpszSourceUrlName)

                    'free the pointer and memory associated
                    'with the last-retrieved file
                    Call LocalFree(pntrICE)
                    dwBuffer = 0
                    Call FindNextUrlCacheEntry(hFile, ByVal 0, dwBuffer)

                    'allocate and assign the memory to the pointer
                    pntrICE = LocalAlloc(LMEM_FIXED, dwBuffer)
                    CopyMemory ByVal pntrICE, dwBuffer, 4
                Loop While FindNextUrlCacheEntry(hFile, ByVal pntrICE, dwBuffer)
            End If
        End If
    End If
    Call LocalFree(pntrICE)
    Call FindCloseUrlCache(hFile)
End Sub

Public Sub ClearHistory()
    Dim objClsid As GUID
    Dim idClsid As GUID
    Dim pvarResult  As Long
    Dim ret As Long
    
    Call CLSIDFromString(StrPtr(CLSID_CUrlHistory), objClsid)
    Call CLSIDFromString(StrPtr(CLSID_IUrlHistoryStg2), idClsid)
    If CoCreateInstance(objClsid, 0&, CLSCTX_INPROC_SERVER, idClsid, pvarResult) = S_OK Then
        If DispCallFunc(pvarResult, IUrlHistoryStg2_ClearHistory, CC_STDCALL, vbLong, 0&, 0&, 0&, ret) = S_OK Then
            DispCallFunc pvarResult, IUrlHistoryStg2_Release, CC_STDCALL, vbLong, 0&, 0&, 0&, ret
        End If
    End If
End Sub
I didn't write this code. Temporary Internet Files and Cookies are both cleared via the ClearCache() sub. I added the strCookie logic to it for my own uses. You can use strCookie to "protect" some of the cookies by name instead of just clearing all of them en masse.

You didn't ask about the TypeAhead, but that's easy enough to clear as well. Simply delete all the values in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\TypedURLs

For more in-depth VBA help I would recommend VBForums.com, specifically the Office Develpment forum.

Last edited by Ellis Dee; 01-10-2010 at 10:45 AM..
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 01:43 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.