Martin Smith
asked on
Excel VBA - Export File from Website
Looking for help with pulling data off a web page using Excel VBA. I've managed to successfully make all the selections and run the report, now I just need the data.
I need help to:
- Select the export to excel option, and
- Save the file to my desired location
Here is my existing VBA code:
This gets me all the way to clicking the export button, but I can't figure out the last step of getting to the excel export option.
HTML code here:
I need help to:
- Select the export to excel option, and
- Save the file to my desired location
Here is my existing VBA code:
Sub macro_07()
Dim appIE As InternetExplorerMedium
'Set appIE = Nothing
Dim objElement As Object
Dim objCollection As Object
Dim objCollection2 As Object
Dim objCollection3 As Object
Dim objCollection4 As Object
Dim objCollection5 As Object
Dim objCollection6 As Object
Dim objCollection7 As Object
Dim objCollection8 As Object
Set appIE = New InternetExplorerMedium
sURL = ThisWorkbook.Sheets("Control").Range("IALinks").Cells(1, 1).Value
With appIE
.Navigate sURL
.Visible = True
End With
Do While appIE.Busy Or appIE.readyState <> 4
DoEvents
Loop
Set objCollection = appIE.document.getElementById("ctl31_ctl04_ctl07_txtValue")
objCollection.Value = Range("YearSlct").Value
Set objCollection2 = appIE.document.getElementById("ctl31_ctl04_ctl03_ddValue")
objCollection2.selectedIndex = 4
objCollection2.FireEvent "onchange"
Application.Wait (Now + TimeValue("0:00:002"))
Set objCollection3 = appIE.document.getElementById("ctl31_ctl04_ctl05")
objCollection3.Click
Set objCollection4 = appIE.document.getElementById("ctl31_ctl04_ctl05_divDropDown_ctl01")
objCollection4.Focus
objCollection4.Checked = "checked"
Set objCollection5 = appIE.document.getElementById("ctl31_ctl04_ctl05_divDropDown_ctl03")
objCollection5.Focus
objCollection5.Checked = "checked"
objCollection5.FireEvent "onchange"
Set objCollection6 = appIE.document.getElementById("ctl31_ctl04_ctl00")
objCollection6.Click
Application.Wait (Now + TimeValue("0:00:002"))
Set objCollection7 = appIE.document.getElementById("ctl31_ctl06_ctl04_ctl00_Button")
objCollection7.Click
Set appIE = Nothing
End Sub
This gets me all the way to clicking the export button, but I can't figure out the last step of getting to the excel export option.
HTML code here:
<table style="display:inline;" cellspacing="0" cellpadding="0">
<tbody><tr>
<td height="28"><div class="HoverButton" id="ctl31_ctl06_ctl04_ctl00">
<table title="Export" id="ctl31_ctl06_ctl04_ctl00_Button" border="0">
<tbody><tr>
<td><a id="ctl31_ctl06_ctl04_ctl00_ButtonLink" style="cursor: pointer;" href="javascript:void(0)"><img id="ctl31_ctl06_ctl04_ctl00_ButtonImg" style="border-style:None;height:16px;width:16px;border-width:0px;" src="/Reports/Reserved.ReportViewerWebControl.axd?OpType=Resource&Version=10.50.1600.1&Name=Microsoft.Reporting.WebForms.Icons.Export.gif"><img id="ctl31_ctl06_ctl04_ctl00_ButtonImgDown" style="border-style:None;height:6px;width:7px;border-width:0px;margin-bottom:5px;margin-left:5px;" src="/Reports/Reserved.ReportViewerWebControl.axd?OpType=Resource&Version=10.50.1600.1&Name=Microsoft.Reporting.WebForms.Icons.ArrowDown.gif"></a></td>
</tr>
</tbody></table>
</div><div class="MenuBarBkGnd" id="ctl31_ctl06_ctl04_ctl00_Menu" style="padding: 1px; display: block; visibility: visible; position: absolute;">
<div class="HoverButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('XML');" href="javascript:void(0)">XML file with report data</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('CSV');" href="javascript:void(0)">CSV (comma delimited)</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('PDF');" href="javascript:void(0)">PDF</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('MHTML');" href="javascript:void(0)">MHTML (web archive)</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('EXCEL');" href="javascript:void(0)">Excel</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('IMAGE');" href="javascript:void(0)">TIFF file</a>
</div><div class="DisabledButton">
<a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('WORD');" href="javascript:void(0)">Word</a>
</div>
<div style="left: 0px; top: 0px; width: 26px; height: 149px; filter: none; position: absolute; z-index: -1; opacity: 0.05; background-color: black;"></div></div></td>
</tr>
</tbody></table>
ASKER
Updated with additional details
What is the value of sURL
Hi Martin.. that link does not work. Is it "dyns-services.com" rather than "dyn-services"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Was able to solve by figuring out how to reference the inntertext.
Without knowing more information (URL, existing code) it is unlikely that a solution can be recommended.