Forum Discussion

Chris_Mugdan's avatar
Chris_Mugdan
Copper Contributor
Jun 08, 2023

Forcing Excel VBA to make a request to a URL

I have some VBA code that fetches a file from a remote server to populate a list box in Excel. I am using Microsoft® Excel® 2021 MSO (Version 2304 Build 16.0.16327.20200) 64-bit 

The VBA code that I am trying to make work is:

result = URLDownloadToFile(0&, _
serverAddress & "\mapIntelligence\clientTemplates?action=list&" & params, _
LocalFilename, _
BINDF_GETNEWESTVERSION & BINDF_NOWRITECACHE, _
0&)

but it always seems to load from a local cache and when I add more entries to the remote file it does not make a request to the server until I restart Excel which is not useful at all.

I also tried clearing the cache prior to making the request, i.e.:

DeleteUrlCacheEntry (serverAddress & "\mapIntelligence\clientTemplates?action=list&" & params)

with this declaration:

Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" _
(ByVal lpszUrlName As String) As Long

 

I have not been able to find anything useful on the Web to solve this problem. Can anyone suggest how I can force the request to the remote server to be made?

Cheers,
Chris Mugdan

excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 08, 2023

    Chris_Mugdan 

    The URLDownloadToFile function in VBA typically caches the downloaded file to improve performance. However, if you want to ensure that the request is made to the remote server and not from the local cache, you can try appending a unique parameter to the URL. This will trick the cache into thinking it is a new request and force it to fetch the latest data from the server.

    Here is an example of how you can modify your code to include a unique parameter:

    Dim uniqueParam As String
uniqueParam = "&timestamp=" & Format(Now(), "yyyymmddhhmmss") ' Append a unique timestamp parameter

result = URLDownloadToFile(0&, _
    serverAddress & "\mapIntelligence\clientTemplates?action=list&" & params & uniqueParam, _
    LocalFilename, _
    BINDF_GETNEWESTVERSION & BINDF_NOWRITECACHE, _
    0&)

    By adding the uniqueParam to the URL, it will change with each request, effectively bypassing the cache and ensuring that the latest data is fetched from the remote server.

    Give this approach a try and see if it resolves the issue with fetching the updated data without having to restart Excel.

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Jun 08, 2023
        I'm glad that they were helped.
        I wish you continued success with Excel!

Resources