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:[email protected]/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:[email protected]/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!
