How can VBA fill-in web form??

[DaveK]

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]

[DaveK]

The attachment of the HTML code did not seem to be sent. Trying again.

[stanl]

' 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?

Not so sure how easy this is in VBA, but I do a similar task each day for 13 different sites, but not using VBA

After a click() is sent to the download button, I present a 'pause' message that says "Waiting for File Download Window - click if it is present", and this pauses for 2 minutes, if a user is running the script they can click the message if the download window appears prior to the 2 minutes.

Then you have to look for the window. In VBA, I imagine something like

[vba]
Set objShell = CreateObject("Shell.Application").
For Each obj In objShell.Windows
text = obj.title
If Instr(text,"File Download")
[/vba]

I then sendkeys to this window if it exists, (it will either be 3 enter keys, or 3 tabs and an enter key, depending on the Version of Windows and Version of IE).

This will bring up a Save As Window - and the sendkeys are 2 tabs and enter. (I also enter a path and specific file name for the download)

Finally a "Download Complete" - sendkeys 3 tabs and enter.

But you seem to be saying you are only asked for the Save As. Is it possible for you to post a screenshot of how the popup appears?

.02 Stan

[DaveK]

Thank You for your tip... I've made some little progress now, using the SENDKEYS idea... although there must be a better way to do it, but this seems to be working. At least I can get the web-based report to RUN.


QUESTION 1:
what is the VBA code to simply activate or focus the Internet Explorer window so that the SENDKEYS actually get sent to THAT WINDOW (and not to my VBA program code window!)

[VBA]
With IE
.Navigate "http://intranet.urlhere.com/cgi/swr/...pl?report=8541"
.Visible = True

Do Until IE.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until .Document.readyState = "complete"
DoEvents
Loop


SendKeys ("{TAB}")
Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{TAB}")
'Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{DOWN}") ' selects EXCEL download format
Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{TAB}") ' move to the STARTING DATE field
Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")

SendKeys ("{TAB}") ' move to the ENDING DATE field

Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")
SendKeys ("{DOWN}")

SendKeys ("{TAB}") ' move to the ENTER field
Application.Wait Now + TimeValue("00:00:01")

SendKeys ("{ENTER}") ' run the report!

''' NOW HERE IS WHERE I NEED MORE HELP


'AT THIS POINT, the web-based report has been requested and now I am
'WAITING FOR the little "FILE DOWNLOAD" window to appear, which indicates that the report is done running and now I need to SAVE IT as .XLS file.

'THERE MUST BE SOME WAY TO automatically look for this WINDOW TO APPEAR... SINCE THE AMOUNT OF TIME IT TAKES VARIES A LOT.

'this is not working yet!

HOW TO FOCUS OR SELECT THIS "FILE DOWNLOAD" window SO THE SENDKEYS DATA GOES TO IT?




SendKeys ("{TAB}")

SendKeys ("{TAB}") ' move to SAVE field

SendKeys ("{ENTER}") ' brings up the file SAVE dialog box
Application.Wait Now + TimeValue("00:00:01")

SendKeys ("2008.06_Report")
Application.Wait Now + TimeValue("00:00:01")
SendKeys ("{ENTER}") ' SAVE IT!


[/VBA]

QUESTIONS:

How to automatically tell once the "FILE DOWNLOAD" window appears? Please show how this can be done in VBA code... there must be a way...

How to select or activate or focus on the desired little window to make sure the SENDKEYS data is sent to it correctly?

THANK YOU SOOO MUCH for your help... automating this will be so wonderful if I can get it to work!

Dave

[TomSchreiner]

Hi Dave. You can download your file directly from the server without using IE. The code below may or may not work because I am only guessing what the request header may be. If it does not work, let me know and we'll try a variation. There are also three other parameters that are not user-defined.

action=U
mode=R
app=swr

These are probably always the same but they may not be.

It is not neccesary to use all of the variables in the ExampleUsage procedure or the enum. I placed them there for readability.

[VBA]Private Enum OutputFormat
HtmlOnline = 1
ExcelCSV = 2
PDF = 4
End Enum

Sub ExampleUsage()
Dim URL As String
Dim ReportId As String
Dim OutPutType As OutputFormat
Dim EarliestClosedDate As String
Dim LatestClosedDate As String
Dim SaveToLocalFile As String

URL = "http://intranet.urlhere.com/cgi/swr/swr_run.pl?"
ReportId = "8541"
OutPutType = ExcelCSV
EarliestClosedDate = "01-Dec-2008"
LatestClosedDate = "31-Dec-2008"
SaveToLocalFile = "C:\Documents and Settings\dk\Desktop\2008.06_data_feed_TEST.xls"

Call GetFileFromServer(URL, ReportId, OutPutType, EarliestClosedDate, LatestClosedDate, SaveToLocalFile)
End Sub

Sub GetFileFromServer(URL As String, _
ReportId As String, OutPutType As OutputFormat, _
EarliestClosedDate As String, LatestClosedDate As String, _
SaveToLocalFile As String)

Dim Request As Object
Dim FileNum As Integer
Dim b() As Byte

On Error Resume Next
Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
If Request Is Nothing Then
Set Request = CreateObject("WinHttp.WinHttpRequest.5")
End If
On Error GoTo 0

With Request
.Open "GET", URL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send "report=" & ReportId & "&action=U&mode=R&app=swr&outputType=" & OutPutType & _
"&parameter_1=" & EarliestClosedDate & "&parameter_2=" & LatestClosedDate
End With

FileNum = FreeFile
Open SaveToLocalFile For Binary As #FileNum
b() = Request.ResponseBody
Put #FileNum, 1, b()
Close FileNum

End Sub[/VBA]

[stanl]

I was thinking HTTP download, but had doubts it would work with 'generated' reports, viz. they don't exist as a static file on the web site. But if it works, ignore everything I wrote. Stan

[DaveK]

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

[stanl]

I wrote an app for distribution to non-profits in North Carolina. It would locate all churches, schools and banks in towns and cities. This was stored in Access with several binary fields, one would hold a mapquest snapshot of each places location.

I obtained the 'maps' by using the xHTTP freeware .ocx [ from www.xstandard.com ]. Not only is it free, but I prefer it often over WinHTTP or MSXML as it incorporates Tidy and can return requests as well-formed XML (which web pages often don't). I could then use xPath to obtain the map via an ADODB Stream.

I have used a similar method to obtain adobe, excel and other binary files as I could re-direct the content to disc as required. Problem is these were/are proprietary sites.

I assume you site is proprietary so we cannot suggest code that we could test.

Stan