Nikit Strygin
New Member
- Joined
- Aug 19, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
- MacOS
I am currently trying to make a VBA code which would multiple files from a given URL. These VBA code should do the following things:
1. Open internet explorer ***in the background*** and download multiple files from a link. Basically, to download multiple files I should change file code in the middle of the URL and the file name in the end of the URL. It could be seen in my code attempt. Each file has it specific code (E.g for File 1 the code is "0001", File 2 - "0002" and etc) and names (File 1 - "FileNamed1", File 2 - "FileNamed2"). In the end of the url there is a wildcard character as the files have dates in the end, so I want excel to ignore them;
2. Rename these documents (E.g So for File1 it should be "File1", for File2 it should be "AnotherFile 2" and etc). Files which are downloaded are in .xlsx format;
3. Save renamed files to the specific folder
So, how to do all these actions through vba?
My attempt is below. Hope I have everything explained clearly. Thanks very much in advance!
1. Open internet explorer ***in the background*** and download multiple files from a link. Basically, to download multiple files I should change file code in the middle of the URL and the file name in the end of the URL. It could be seen in my code attempt. Each file has it specific code (E.g for File 1 the code is "0001", File 2 - "0002" and etc) and names (File 1 - "FileNamed1", File 2 - "FileNamed2"). In the end of the url there is a wildcard character as the files have dates in the end, so I want excel to ignore them;
2. Rename these documents (E.g So for File1 it should be "File1", for File2 it should be "AnotherFile 2" and etc). Files which are downloaded are in .xlsx format;
3. Save renamed files to the specific folder
So, how to do all these actions through vba?
My attempt is below. Hope I have everything explained clearly. Thanks very much in advance!
VBA Code:
Sub LinkFromURL()
Dim IE As Object, HTMLDoc As Object, URL$
Application.ScreenUpdating = False
Set IE = CreateObject("internetexplorer.application")
filenames = Array("File1", "File2")
filecode = Array("0001", "0002")
URLPath = "https://somesite/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