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]

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

Thanks for looking into this for me.
It is an excel file. (.xls)

I will change and test.

Oh, BTW I had double posted in an attempt to solve my issue as quickly as possible.
No response has been posted on VBAforum

Thanks again.
I'll let you know how it goes

 

[LBicker]

It "errors out" on the localFile command no matter which directory I input...
Can you help with this as well?

I think it has to deal with part of my original need to be able to overwrite the existing file.
It works with new file creation.

Thanks again


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

 

[LBicker]

No worrries.
I did some digging, while I worked on other things and solved my own question.


.SaveToFile localFile, 2

(adding the ", 2" tells the code to overwrite)

Thanks for all your help, and this forum string can be closed for all my purposes.

Cheers

 

[John_w]

No worrries.
I did some digging, while I worked on other things and solved my own question.


.SaveToFile localFile, 2

(adding the ", 2" tells the code to overwrite)

Thanks for all your help, and this forum string can be closed for all my purposes.

Cheers

Well done. Please make sure you post a link to this thread in your thread on vbforums. Why? Read Excelguru Help Site - A message to forum cross posters

If you want to download other files in response to different form input values, just change the values in this line as necessary:

POSTdata = "__VIEWSTATE=" & Escape(ViewStateInput.Value) & "&__EVENTVALIDATION=" & Escape(EventValidationInput.Value) & _
        "&txtPlant=B1&txtProcess=C250&txtNoMonths=5&ex=Get+ProductionPlan"

 

[LBicker]

Well done. Please make sure you post a link to this thread in your thread on vbforums. Why? Read Excelguru Help Site - A message to forum cross posters

If you want to download other files in response to different form input values, just change the values in this line as necessary:

POSTdata = "__VIEWSTATE=" & Escape(ViewStateInput.Value) & "&__EVENTVALIDATION=" & Escape(EventValidationInput.Value) & _
        "&txtPlant=B1&txtProcess=C250&txtNoMonths=5&ex=Get+ProductionPlan"

This is now done.
To all who view this, the alternate link is here.

Excel VBA automation for Internet Explorer to FTP site-VBForums

 

[Ghouul]

Hi everyone.

I was looking for a solution to a similar problem.
I don't really understand how I should change the POSTdata variable to fit my request

Can you go a bit more in detail in how I get thos parameters?

Thanks in advance.

 

[John_w]

Whilst browsing manually use the browser's console tool to examine the GET/POST request body, or use Fiddler and examine the raw data in the Inspector for the relevant session.

If you need specific help please start your own thread and post the URL.