Firstly, I should state that my knowledge of the Microsoft Internet Controls and MSHTML libraries is low. I started using
them recently to try to automate the downloading of excel reports from a webform. As such, this has been a learning
experience, and my code definitely reflects that.
etc. The result is that the page requires a generous amount of navigation to get to the input elements and to the export link
(My guess is there is a better way for this as well, but that is not in scope for this post). I want to fill in the form,
then click the link to export the results to excel.
I have been able to fill in the form and get it to fire the export link. I am stuck here.
After I export the
report, IE (I am restricted to IE at work...) opens a new window and then an Excel dialogue box opens with options to open,
save, or cancel the document sent from the server. The file name of the export is not constant, and I don't know how to get
the path of the export to feed to Excel.
I know methods to access the workbook after I open it, but I am stuck on
how to actually open the workbook. Any help would be appreciated.
Obviously, posting the URL would be the best
way, but it is restricted, so I cannot.
Dim appIE As InternetExplorer
Dim doc1 As HTMLDocument
Dim doc2 As IHTMLElementCollection
Dim doc3 As IHTMLElementCollection
Dim doc4 As HTMLFrameElement
Dim doc5 As IHTMLElementCollection
Dim doc6 As HTMLFrameElement
Dim doc7 As HTMLDocument
Dim doc8 As HTMLFormElement
Dim doc9 As IHTMLInputElement
Dim doc10 As IHTMLTextAreaElement
Dim doc11 As HTMLFrameElement
Dim doc12 As HTMLDocument
Dim doc13 As HTMLLinkElement
Dim wbCount As Integer
Dim newWb As Integer
Dim wsCount As Integer
Dim downloadedWb As Workbook
Dim userDate As String
Dim userCusip As String
Application.DisplayAlerts = False
'specify user inputs here: parameterize after the rest of the code is working...
userDate = "11/30/2011"
userCusip = "248019AG6"
wbCount = Workbooks.Count
newWb = wbCount + 1
wsCount = ThisWorkbook.Sheets.Count
'setting the URL that has the webform...
sURL = "https://xxx/search_tools.epl"
Set appIE = New InternetExplorer
'open the webform and display to user
appIE.Visible = True
'loop until ready
Do While appIE.Busy
Do Until appIE.readyState READYSTATE_COMPLETE
'the JScript does not seem to fire the readystate complete...so wait 10 seconds to be sure
Application.Wait (Now + TimeValue("00:00:10"))
'set the location of the input elements
Set doc1 = appIE.document
Set doc2 = doc1.getElementsByTagName("form")
Set doc3 = doc1.all
Set doc4 = doc3.Item("tool_frame")
Set doc5 = doc4.contentDocument.all
Set doc6 = doc5.Item("content_frame")
Set doc7 = doc6.contentDocument
Set doc8 = doc7.forms(, 1)
'find the pos date and update to be the user specified date
For Each doc9 In doc8.getElementsByTagName("INPUT")
If InStr(doc9.Name, "pos_date") Then
doc9.Value = userDate
'find the altid and update to be the user specified CUSIP
For Each doc10 In doc8.getElementsByTagName("TEXTAREA")
If InStr(doc10.Name, "altid") Then
doc10.Value = userCusip
'set the path for the export link
Set doc11 = doc5.Item("header_frame")
Set doc12 = doc11.contentDocument
'find the export link via the outerHTML word export
For Each doc13 In doc12.Links
If InStr(doc13.outerHTML, "Export") Then
'Application.Wait (Now() + TimeValue("00:00:10"))
'Set downloadedWb = Workbooks(newWb)
Application.DisplayAlerts = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines