Excel VBA automation for Internet Explorer to web site

[LBicker]

I have been trying for some time to be able to download a file from what I believe to be a FTP site.
There are three user input fields which I have been able to identify there names.
After these fields are complete there is a button to push which I have also identfied it's name.

Once the process of pushing this button is done, an unspecified amount of time must occur for data to be gathered and the option of Open, Save, or Canel appear in a Download window.
I would then need to Save and Overwrite an existing file.

No Login or Password is required

Thanks for your consideration and feedback.
Any and all help is appreciated.

I have been able to automate the open of I.E. and input to the three select fields as well as the necessary button push.
However, I am unable to identify how to automatically save the file into a specified location, with overwrite priviledges enabled.
Below is my progress

 

[LBicker]

Also, I am using Excel 2003 on a machine running Windows 7 with Internet Explorer version 8.

Thanks again

 

[John_w]

Firstly, it will be far easier to help if you post the URL.

If you think it's an FTP site, why are you using IE automation and http?

Does clicking the button (I assume you mean the 'Get Production' button - please be as specific as possible when asking for help) produce a URL in the browser address bar with the input parameters in it (e.g. it might be http://ppd/xxx.xxx?plant=B1&process=C250&months=5, based on your screenshot)? Have a look at the HTML source or use Fiddler Web Debugger to find out, or post the HTML here. Even if the URL in the address bar doesn't change to reflect the input parameters, a specific URL might be being requested behind the scenes, based on the input fields. If it is a specific URL, then in Excel try: File - Open - the URL.

 

[LBicker]

Firstly, it will be far easier to help if you post the URL.

If you think it's an FTP site, why are you using IE automation and http? IE is the source for the information I am needing and I am not sure if it is FTP, but I do know it is a company intranet site. Sorry if I mispoke.

Does clicking the button (I assume you mean the 'Get Production' button - please be as specific as possible when asking for help) produce a URL in the browser address bar with the input parameters in it (e.g. it might be http://ppd/xxx.xxx?plant=B1&process=C250&months=5, based on your screenshot)? Not that I can tell other than what is showing in the picture attached below (/Default.aspx). Have a look at the HTML source or use Fiddler Web Debugger to find out, or post the HTML here. Even if the URL in the address bar doesn't change to reflect the input parameters, a specific URL might be being requested behind the scenes, based on the input fields. If it is a specific URL, then in Excel try: File - Open - the URL.

John_w,
Thanks for helping me with my question.
You are correct in your assumption of the "Get Production" being clicked.
I have replied in-line to your response. See Bold

I ran the suggested Program (Fiddler).
I am not sure how to read it's inputs, but I did save the session and could possibly post it if only I knew how.
Thanks again.

 

[LBicker]

John_w,
Thanks for helping me with my question.
You are correct in your assumption of the "Get Production" being clicked.
I have replied in-line to your response. See Bold

I ran the suggested Program (Fiddler).
I am not sure how to read it's inputs, but I did save the session and could possibly post it if only I knew how.
Thanks again.

I cannot seem to be able to post a picture as I had before.
Does drag and drop not work. or is URL the only way...

Thanks and sorry for any confusion

 

[LBicker]

?

 

[John_w]

John_w,
Thanks for helping me with my question.
You are correct in your assumption of the "Get Production" being clicked.
I have replied in-line to your response. See Bold

I ran the suggested Program (Fiddler).
I am not sure how to read it's inputs, but I did save the session and could possibly post it if only I knew how.
Thanks again.

Your screenshots look like http rather than ftp, so IE automation or direct download from a specific URL is the method to use.

Fiddler - In the Web Sessions window select the session which is actually requesting the file for download (it produces the download window with Open, Save and Cancel buttons), then click the Inspectors tab on the right hand side and within that select the Raw tab and click 'View in Notepad' and post the contents of the Notepad window here inside [ CODE ] or [ HTML ] tags. Do the same for 2 or 3 sessions before the download session (text/html requests, not image requests). It will be easier if you upload the Fiddler saved session (.saz) file (maybe put it in a .zip file) to a free file sharing site and post the link here.

With it being an intranet site I won't be able to test any code for you.

 

[LBicker]

Your screenshots look like http rather than ftp, so IE automation or direct download from a specific URL is the method to use.

Fiddler - In the Web Sessions window select the session which is actually requesting the file for download (it produces the download window with Open, Save and Cancel buttons), then click the Inspectors tab on the right hand side and within that select the Raw tab and click 'View in Notepad' and post the contents of the Notepad window here inside [ CODE ] or [ HTML ] tags. Do the same for 2 or 3 sessions before the download session (text/html requests, not image requests). It will be easier if you upload the Fiddler saved session (.saz) file (maybe put it in a .zip file) to a free file sharing site and post the link here.

With it being an intranet site I won't be able to test any code for you.

POST http://ppd/Default.aspx HTTP/1.1
Accept: application/x-ms-application, image/jpeg, application/xaml+xml, image/gif, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*
Referer: http://ppd/
Accept-Language: en-US
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.1; .NET4.0E)
Content-Type: application/x-www-form-urlencoded
Accept-Encoding: gzip, deflate
Host: ppd
Content-Length: 257
Connection: Keep-Alive
Pragma: no-cache
__VIEWSTATE=%2FwEPDwUKLTgyOTE4OTk0MmQYAQUJR3JpZFZpZXcxD2dk1HYILKVu6AMeGPm2rEbmHeEhUTg%3D&__EVENTVALIDATION=%2FwEWBgLjufy6CALIkpGICwLs7f%2BnDQKDmMHvBwK774rwDAK7q7GGCOinHSHcNYqZ%2FloM88RFOLRmkPiO&txtPlant=B1&txtProcess=C250&txtNoMonths=5&ex=Get+ProductionPlan

 

[John_w]

That was helpful. What file type is being downloaded? Is it .txt, .csv, .xls, etc. or something else? In the following code I've assumed it's .txt, but change the file name shown in the code as necessary. You shouldn't need to change any other part of the code, however I have no way of testing it, but it should be pretty close. Put this code in a new standard module and run or call XMLhttp_Download_File.

Option Explicit

Public Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" _
    (ByVal lpszUrlName As String) As Long
    
 
Public Sub XMLhttp_Download_File()

    Dim URL As String
    Dim httpReq As Object       'Or As XMLhttp for early binding - add reference to MS XML v6.0
    Dim POSTdata As String
    Dim HTMLdoc As Object       'Or As HTMLDocument - add reference to MS HTML Object Library
    Dim ViewStateInput As Object, EventValidationInput As Object
    Dim ADODBstream As Object
    Dim localFile As String
    
    'The file name which the downloaded file will be saved as
    
    localFile = "C:\folder\file.txt"            'CHANGE THIS STRING
    
    URL = "http://ppd/Default.aspx"
    DeleteUrlCacheEntry URL
 
    Set httpReq = CreateObject("Microsoft.XMLHTTP")     'Or = New XMLhttp for early binding
    
    'Request initial page to get __VIEWSTATE and __EVENTVALIDATION values
    
    With httpReq
        .Open "GET", URL, False
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.1; .NET4.0E)"
        .send
         
        'Put response in a HTMLDocument
        
        Set HTMLdoc = CreateObject("HTMLFile")  'late binding
        'Set HTMLdoc = New HTMLDocument         'early binding
        HTMLdoc.body.innerHTML = .responseText
    End With

    Set ViewStateInput = HTMLdoc.getElementById("__VIEWSTATE")
    If ViewStateInput Is Nothing Then
        MsgBox "Error retrieving __VIEWSTATE from " & URL & String(2, vbCrLf) & HTMLdoc.body.innerText
        Exit Sub
    End If
    
    Set EventValidationInput = HTMLdoc.getElementById("__EVENTVALIDATION")
    If EventValidationInput Is Nothing Then
        MsgBox "Error retrieving __EVENTVALIDATION from " & URL & String(2, vbCrLf) & HTMLdoc.body.innerText
        Exit Sub
    End If
    
    'Construct the POST data string with the form parameters
     
    POSTdata = "__VIEWSTATE=" & Escape(ViewStateInput.Value) & "&__EVENTVALIDATION=" & Escape(EventValidationInput.Value) & _
        "&txtPlant=B1&txtProcess=C250&txtNoMonths=5&ex=Get+ProductionPlan"
    
    'Send POST data to request file

    With httpReq
        .Open "POST", URL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setRequestHeader "Content-Length", Len(POSTdata)
        .send (POSTdata)        'brackets are compulsory only for late binding of XMLHTTP
         
        'Save response in local file
        
        If .Status = 200 Then
            Set ADODBstream = CreateObject("ADODB.Stream")
            With ADODBstream
                .Type = 1 'adTypeBinary
                .Open
                .write httpReq.responseBody
                .SaveToFile localFile
                .Close
            End With
       End If
    End With
    
End Sub


Private Function Escape(ByVal URL As String) As String

    Dim i As Integer, BadChars As String

    BadChars = "<>%=&!@#$^()+{[}]|\;:'"",/?"
    For i = 1 To Len(BadChars)
        URL = Replace(URL, Mid(BadChars, i, 1), "%" & Hex(Asc(Mid(BadChars, i, 1))))
    Next
    URL = Replace(URL, " ", "+")
    Escape = URL

End Function

 

[John_w]

Cross-posted - Excel VBA automation for Internet Explorer to FTP site-VBForums.

Please put a link in that thread to this thread so that people on that forum know that I'm helping you, otherwise their efforts may be duplicated or wasted. Put reciprocal links in all other forum threads which you've posted this question on.