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.
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.
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