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

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

[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

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

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!)

  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!

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.

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

[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