How can VBA fill-in web form??
I am still learned VBA and recently read that VBA can automatically do Internet Explorer.. and I do lots of intranet web page reports, and would love to have VBA to do this.
See the below VBA code, and also the attachment of the web page HTML from where the webpage asks for the user to enter choices before I run the web-based report.
Your help is greattttllyyyy appreciated!
Dave
[VBA]
Sub IE_test()
'
' set reference to Microsoft Internet Controls
Dim IE As InternetExplorer
Set IE = New InternetExplorer
Dim YearMonth As String
Dim DataFeedPath As String
Dim DataFeedfilename As String
Dim DataFeedFullPath As String
Dim Path_to_Desktop As String
Path_to_Desktop = "C:\Documents and Settings\dk\Desktop\"
DataFeedfilename = "2008.06_data_feed_TEST.xls"
DataFeedFullPath = DataFeedPath & DataFeedfilename
With Session
With IE
.Navigate "http://intranet.urlhere.com/cgi/swr/...t=8541&tries=1"
.Visible = True
Do Until IE.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until .Document.readyState = "complete"
DoEvents
Loop
' HERE IS WHERE I NEED HELP. I DONT KNOW WHAT CODE NEEDS TO BE HERE
' THIS BELOW CODE IS A GUESS BASED ON OTHER POSTINGS I SAW
.Document.Forms(1).Elements("radio").Value = "2"
.Document.Forms(1).Elements(1).Value = "01-Jun-2008"
.Document.Forms(1).Elements(2).Value = "30-Jun-2008"
End With
End With
' WHAT HAPPENS AFTER THE WEB_BASED report is RUN (takes about 60 seconds)
' is that a dialog box appears and asks where to SAVE the excel file.
' HOW can this step be automated as well?
' ONCE I FIGURE THIS OUT, this procedure would run a series of different reports,
' resulting in several EXCEL files being saved into a specific folder on my PC
End Sub
[/VBA]
how to run a dynamic http report and pull into excel
Quote:
Originally Posted by TomSchreiner
FileNum = FreeFile
Open SaveToLocalFile For Binary As #FileNum
b() = Request.ResponseBody
Put #FileNum, 1, b()
Tom, Stan, THANK YOU for helping. Stan is correct, that this report is dynamic and as such there is no file already sitting there at this URL... once I hit submit with the chosen parameters, it takes 30-60 seconds and then a FILE DOWNLOAD dialog box appears, asking if I want to save the EXCEL file.
So I dont think this will work. I actually tried it... and it DOES SOMETHING (maybe sent the request to this URL with the parameters...) and then the macro fails at
Open SaveToLocalFile For Binary As #FileNum
with PATH NOT FOUND. I know my SaveToLocalFile path is valid as it is pointing to the desired EXCEL file on my desktop.
Anyway, I dont understand this part :
Open SaveToLocalFile For Binary As #FileNum
b() = Request.ResponseBody
Put #FileNum, 1, b()
I really appreciate your help by looking at the webpage html code and getting this far. I really am hoping you or someone can figure this out and help me solve this... either using SENDKEYS as discussed above and the issues I still open with that approach, and a more robust approach like you have suggested... if only we can get it to work! THANKS and I look forward to some replies.
Dave