How to download files from websites with VBA based on workdays

My trouble now is how can I automate the VBA do add +1 in the URL all new day? Or to copy from a workbook the link using the day I have set?

This is the URL : "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id=61794" for 10/08/2021, and this number "61794" in the end of URL always get +1 to the next workday, I need to make the VBA download that file all workdays.

This is my code to download from web with a directly download link

    Option Explicit

 #If VBA7 Then
     Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
         ByVal pCaller As LongPtr, _
         ByVal szURL As String, _
         ByVal szFileName As String, _
         ByVal dwReserved As LongPtr, _
         ByVal lpfnCB As LongPtr) As LongPtr
 #Else
     Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
         ByVal pCaller As Long, _
         ByVal szURL As String, _
         ByVal szFileName As String, _
         ByVal dwReserved As Long, _
         ByVal lpfnCB As Long) As Long
 #End If

Sub Web_Taxas()

    Dim FileURL As String
    Dim DestinationFile As String
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("links")
   
    FileURL = "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id=61797"
    DestinationFile = "C:\Temp\BASES\TBEX-OB08\todasasmoedas.csv"
    
    If URLDownloadToFile(0, FileURL, DestinationFile, 0, 0) = 0 Then
        Debug.Print "File download started"
    Else
        Debug.Print "File download not started"
    End If
    
    'Call Refresh_Base
    
End Sub

WORKDAYS - URL - LAST DIGITS

Solution

If the date you show us (10/08/2021) means 10th of August, try this code to define the necessary suffix for today, please:

Sub testFileURL()
   Dim fileURL As String
   Const strURL As String = "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id="
   fileURL = strURL & getURLsuffix(Date)
   Debug.Print fileURL
   'testing the example date:
   Debug.Print strURL &  getURLsuffix(DateValue("10/08/2021"))
End Sub

Function getURLsuffix(D As Date) As String
    getURLsuffix = CLng(D) + 17376
End Function

In order to use the suggestion in your existing code you should copy the above function and replace in your code:

FileURL = "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id=61797"

with:

FileURL = "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id=" & getURLsuffix(Date)

to download the file for today, or:

FileURL = "https://ptax.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVTodasAsMoedas&id=" & getURLsuffix(DateValue("10/08/2021"))

to download the file for "10/08/2021", which, of course may be any necessary date...