Download multiple files from FTP server with VBA/Macros in Excel

[cg13]

Hi all,
trying to download multiple files from a FTP server. Currently I can download one file from the FTP server but I struggle to modify the code to my needs.
I have the following code:

Option Explicit
 
Private Declare PtrSafe 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
 
Private Declare PtrSafe Function DoFileDownload Lib "shdocvw.dll" (ByVal lpszFile As String) As Long
 
Public Sub test1a() 'ohne Dialog
 
Call URLDownloadToFile(0, "ftp://UserName:Passwort@ftp.abc.de/Week_202251/1_REPORT.ABC_20.12.22.07.32_202251.csv", _
"C:/Users/xyz/Downloads/1_REPORT.ABC_20.12.22.07.32_202251.csv", 0, 0)
 
End Sub

The issue, I am facing is that the filename changes every week since it has a timestamp. The filename structure is:

• 1_REPORT.ABC_TimeStamp
• 2_REPORT.ABC_TimeStamp
• 3_REPORT.ABC_TimeStamp
• 4_REPORT.ABC_TimeStamp
• 5_REPORT.ABC_TimeStamp

Also, the folder where the files are located changes on a weekly basis: Week_202250, Week_202251, and so on. How can I change the code to get all five files with from the changing folder name?
I already tried to use:

Call URLDownloadToFile(0, "ftp://UserName:Passwort@ftp.abc.de/Week_202251/1_REPORT.ABC*.csv", .........

This did not work.

I am looking for a solution where the folder name is calculated by the current week number and the macro downloads all files from this folder.
Thanks for your help!

 

[John_w]

URLDownloadToFile requires an exact file name, so you can't use wildcards with it.

To download multiple files, the code at Download File from FTP site using VBA, which downloads a single file, should work with a few changes. For one, change the ftp get command, which downloads a single file, to mget which downloads multiple files matching a wildcard filespec.

For the current week number, use Application.WorksheetFunction.IsoWeekNum(Date)