(Solved) – vba download excel files from website where filename changes

(solved)-–-vba-download-excel-files-from-website-where-filename-changes

Thankyou for your help. I am trying to create a macro that will download files from the PPAC website. The code below works flawlessly, however the filename changes each month and isn’t always logical (ie, the file will always contain a ddmmyyyy, but that date may be any day in the month).

Can anyone help me to think of a way to download the file with a general name?
One route I’m hoping will be useful is the HTML tags – the filename is shown within the the following the following

Installed Refinery Capacity

So then, the bullet point below heading “Installed Refinery Capacity” contains my file name, which will be consistent except for the date. Can i use that?

Another option is to loop through a number of dates until i hit upon the correct one, that approach im sure will work, but im not learning much..

thanks you for your guidence.

Sub DownloadFile()

Dim myURL As String

'myURL = "https://www.ppac.gov.in/WriteReadData/userfiles/file/PT_installed_24-04-2020.xls?your_query_parameters"
myURL = "https://www.ppac.gov.in/WriteReadData/userfiles/file/PT_installed_24-04-2020.xls"
'myURL = Cells(10, 3)


Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:tmpfile.csv", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *