0

I want to make a VBA code which would download multiple files from a given URL using Internet Explorer. Code should execute following steps:

  1. Opens IE in the background and download files from a URL link. Basically, to download multiple files, the file code in the middle of the URL should be changed as well as the file name in the end of the URL. So, each file has it specific code (E.g for the first file the code is "45551", for second file - "45552" and etc) and names (first file - "ReportRU2021.10.04.xlsx", second file - "ReportUA2021.10.04.xlsx"). As you can see in names of files there is a date and I want to ignore it, so it seems to me wildcard character "*" can be used.
  2. After step 1, the code should rename these documents by removing the date in the end (E.g So for the first file it should be "ReportRU.xlsx", for File2 it should be "ReportUA.xlsx" and etc). Files which are downloaded are in .xlsx format;
  3. Save renamed files to the specific folder

I tried to make all the above steps as a code and my attemp is below. So can anyone help me with all of these steps? Thank you very much for your help in advance!

 Sub FileFromURLDownloader()
    Dim IE As Object, HTMLDoc As Object, URL$
    Application.ScreenUpdating = False
    Set IE = CreateObject("internetexplorer.application")
    filenames = Array("ReportRU*", "ReportUA*")
    filecode = Array("45551", "45552")
    URLPath = "https://website/api/v1/reportdata/" & filecode & "/filename/" & filenames & "*" & ".xlsx"
    For Each file In filenames
    With IE
        .navigate (URL)
        .Visible = False
    End With
    Application.SendKeys "{TAB}{TAB}{ENTER}"
    Application.ScreenUpdating = True
    End Sub
Nikita
  • 35
  • 5
  • What is the specific problem you're having here? FYI probably should make your IE instance visible, or you'll have a difficult time getting this working when you can't see what's going on... – Tim Williams Oct 07 '21 at 16:30
  • Well, the first problem I'm trying to solve is that the IE starts to open when I execute this code because of the popping window which gives me an option to open/save/save as for the files. I want to do this in the background. Second problem is that I don't know how to download multiple files at once as I can't assign the filecode to the filename (e.g ReportRU*- 45551, ReportUA*-45552. And the last problem, I don't know how to rename and save downloaded filles from IE to the specifc folder – Nikita Oct 07 '21 at 16:34
  • Lots of questions here which will be difficult to answer without more details on the API you want to use. A single request can only receive a single response/file, so it's unclear how you'd download multiple files with one request, unless the API provides a zipped collection of files in one response. – Tim Williams Oct 07 '21 at 16:41
  • Okey, I have direct links to download files. You just can pass the URL to the search bar and the file will be downloaded. I need to download 4 specific files for which I've got direct links, so it seems to me it is possible to make an array of these links and the code will execute all the operations written above for all these links. Is it possible to do? – Nikita Oct 07 '21 at 16:54
  • You don't need to use IE just to download a file - eg see https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer – Tim Williams Oct 07 '21 at 16:55
  • I've already tried to make a code which wouldn't use IE, but the thing is that the website which contains files requires me to authorize on the webpage, but with Internet Explorer its already uses my credential for my windows account and I can easily download files – Nikita Oct 07 '21 at 17:00

0 Answers0