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



## cg13 (Dec 29, 2022)

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 (Dec 29, 2022)

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


----------

