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.
The task:
I have a webform which is all scripted in Java. After initializing the page, a javascript determines what tab to display, 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.
The problem:
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.
The code:
Sub positionsDownload()
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.Navigate sURL
appIE.Visible = True
'loop until ready
Do While appIE.Busy
Loop
Do Until appIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'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
Exit For
End If
Next doc9
'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
Exit For
End If
Next doc10
'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
doc13.Click
Exit For
End If
Next doc13
'Application.Wait (Now() + TimeValue("00:00:10"))
'Set downloadedWb = Workbooks(newWb)
'downloadWb.Sheets(1).Move After:=ThisWorkbook.Sheets(wsCount)
Application.DisplayAlerts = True
End Sub