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