Download File that requires user input beforehand

[AnswerSeeker]

Hello folks,

for my work I regularly have to download data files from this page: https://www.regelleistung.net/ext/data/

There, I have to choose the time frame and two other options. To actually download it I have to check the box "Herunterladen". Final step is hitting the submit-button ("anzeigen") and the file is saved automatically in the standard 'download' folder. My goal is to automate this process in excel: user input for all four options should be taken from input cells in excel.

My problem is to not be able to download it. I could not figure out a complete URL-address, since the file is only generated after hitting the submit-button. Instead of I tried it via automatically hitting the buttons, see code below (copied it from other forums):



Sub Test()

Dim ie As Object
Dim frm As Variant
Dim element As Variant

wbDir = ActiveWorkbook.Path
wbAE = ActiveWorkbook.Name

startdate = Format(Workbooks(wbAE).Worksheets("test").Range("E11"), "dd.mm.yyyy")
enddate = Format(Workbooks(wbAE).Worksheets("test").Range("E12"), "dd.mm.yyyy")


' Create InternetExplorer Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

' Send the form data To URL As POST binary request
ie.navigate "https://www.regelleistung.net/ext/data/"

' Wait while IE loading...
While ie.Busy
DoEvents
Wend

ie.document.getElementById("form-from-date").Value = startdate
ie.document.getElementById("form-to-date").Value = enddate
ie.document.getElementById("form-tso").Value = "6"
ie.document.getElementById("form-type").Value = "RZ_SALDO"
ie.document.getElementById("form-download").Checked = True
ie.document.getElementById("submit-button").Click

End Sub

When I execute the code, an IE window pop up with all options chosen correctly. However, I am still unable to download it indicated by field end date ("bis") which is greyed out. Only after I re-ckecked the checkbox ("herunterladen") I am able to download it (indicated by white background of the field for end date).

I hope you can help.
Cheers

[Fennek]

Hi,

just an ideea:

you are handling the query like a user doing by hand. Isn't possible do prepare the detail and pass it with the html-post?

In a little test the filename was "ABGERUFENE_MRL_BETR_SOLL-WERTE_20170601_20170601_50Hertz_20170601-125432.xlsx", the constants at the beginning and the time could be generated before.

Was is about power-query? Have you tried?

regards

(why do don't ask in a German forum?)

[AnswerSeeker]

I tried URLDownloadToFile before, but it did not work despite correct file name (see below). For this reason I tried the other approach posted earlier.


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


Sub DataUpdate()

Application.ScreenUpdating = False

wbDir = ActiveWorkbook.Path
wbAE = ActiveWorkbook.Name


Dim wbArr(3)
wbArr(1) = "ABGERUFENE_MRL_BETR_SOLL-WERTE.CSV" 'https://www.regelleistung.net/download/ABGERUFENE_MRL_BETR_SOLL-WERTE.CSV
wbArr(2) = "ABGERUFENE_SRL_BETR_IST-WERTE.CSV" 'https://www.regelleistung.net/download/ABGERUFENE_SRL_BETR_IST-WERTE.CSV
wbArr(3) = "RZ_SALDO_BETR_IST-WERTE_" 'RZ_SALDO_BETR_IST-WERTE_20170509_20170517_Netzregelverbund_20170530-161118



Dim startdate As Date
Dim startyear As String
Dim startmonth As String
Dim startday As String

Dim enddate As Date
Dim endyear As String
Dim endmonth As String
Dim endday As String

Dim currentdate As Date
Dim currentyear As String
Dim currentmonth As String
Dim currentday As String

currentdate = Workbooks(wbAE).Worksheets("test").Range("E9")
currentyear = Format(Year(currentdate), "0000")
currentmonth = Format(Month(currentdate), "00")
currentday = Format(Day(currentdate), "00")
today = currentyear & currentmonth & currentday

hours = Format(Hour(Now), "00")
minutes = Format(Minute(Now), "00")
seconds = Format(Second(Now), "00")
DownloadTime = hours & minutes & seconds


startdate = Workbooks(wbAE).Worksheets("test").Range("E11")
startyear = Format(Year(startdate), "0000")
startmonth = Format(Month(startdate), "00")
startday = Format(Day(startdate), "00")
Start = startyear & startmonth & startday



enddate = Workbooks(wbAE).Worksheets("test").Range("E12")
endyear = Format(Year(enddate), "0000")
endmonth = Format(Month(enddate), "00")
endday = Format(Day(enddate), "00")
last = endyear & endmonth & endday




For i = 3 To 3
DownloadFile = wbArr(i) & Start & "_" & last & "_Netzregelverbund_" & today & "-" & DownloadTime & ".CSV" 'here the name with extension
'MsgBox DownloadFile
Url = "https://www.regelleistung.net/ext/data/" & DownloadFile 'Here is the web address ' https://www.regelleistung.net/download/RZ_SALDO_BETR_IST-WERTE.CSV
MsgBox Url
LocalFilename = wbDir & "\" & DownloadFile 'here the drive and download directory
x = URLDownloadToFile(0, Url, LocalFilename, 0, 0) = 0


Next i


Application.ScreenUpdating = True


End Sub

P.S.: There are just way more forum posts in English