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.

Here’s VB6 code, which generally works fine in VBA.

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 MAX_CACHE_ENTRY_INFO_SIZE As Long = 4096

Private Const LMEM_FIXED As Long = &H0
Private Const LMEM_ZEROINIT As Long = &H40

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

    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 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
                    '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, specifically the Office Develpment forum.