run report on company intranet and import to excel

[MartinL]

I need to run a report on our company's intranet web page and import it to
excel automatically. I posted this previously on 3-24 but could not get it to
work with the suggestions received.

First I need to open our companies internal web page:
http://webha.kenmex.paccar.com/edc/default.aspx

and select the "1EDCMAT" report, here is part of the source code:

</select><br>
<span id="lblSelectReport">Select a saved report to
automatically load it</span><br>
<select name="selSavedReports"
onchange="__doPostBack('selSavedReports','')" language="javascript"
id="selSavedReports" tabindex="63">
<option value="0">-- Select a Report --</option>
selected="selected" value="932">1EDCMAT</option>

and then I need to select todays date on the "Initiate Date" field:

<td><span id="lblInitiateDt">Initiate Date</span></td><td>
<table cellspacing="0" cellpadding="0">
<tr>
<td valign="top" style="WIDTH:80px"><input
name="txtInitiateDtFrom" type="text" id="txtInitiateDtFrom" tabindex="26"
onkeypress="return noenter()"
style="height:21px;width:80px;POSITION:absolute" /></td>
<td><IMG
onclick="javascript:OpenCalendar('document.frmReport.txtInitiateDtFrom')"
src="images/popupCalendarButton.gif"></td>
<td> - </td>
<td valign="top" style="WIDTH:80px"><input name="txtInitiateDtTo"
type="text" id="txtInitiateDtTo" tabindex="27" onkeypress="return noenter()"
style="height:21px;width:80px;POSITION:absolute" /></td>
<td><IMG
onclick="javascript:OpenCalendar('document.frmReport.txtInitiateDtTo')"
src="images/popupCalendarButton.gif"></td>
<td><span id="lblOptional6">(Optional)</span></td>
</tr>
</table>
</td>
</tr><tr>

and then hit the "Submit" button. After this an "Export to Excel" button
appears so I would like for this to be automatically imported into a specific
sheet in an excel file.

The thing is I would like to have this recorded as a VBA macro so any user
can run on their pc and import into excel.

This is as far as we got on the previous post and have no idea why it isn't
working. I get the following error: "Object variable or With block variable
not
set (Error 91)"


Sub Test()
'references to: _
(1) shdocvw (Microsoft Internet Controls) _
(2) mshtml (Microsoft HTML Object Library)


Dim ie As SHDocVw.InternetExplorer
Dim varHtml As MSHTML.HTMLDocument

Set ie = New SHDocVw.InternetExplorer

With ie
..Visible = True
..Navigate2 "http://webha.kenmex.paccar.com/edc/default.aspx"

Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

End With

'Stop

Set varHtml = ie.Document

With varHtml

' ----------- this is where I get the error on any of these 2 lines, if I
comment the first one I get it on the second and viceversa--------------
MsgBox "I managed to read the label. It says: " _
& .getElementById("lblSelectReport").innerText

'MsgBox "I managed to read the label. It says: " _
& .getElementById("lblSelectReport").innerHTML


' ------ if I comment both lines above I also get the same error here with
any of the following 2 lines:
..getElementById("selSavedReports").Value = 932
'.getElementById("selSelectReport").Value = “932â€

..getElementById("lblInitiateDt").Value = Format(Now, "mm-dd-yyyy")
..getElementById("btnSubmit").Click

'wait until IE finished loading the page
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
End With

End Sub

In the previous post they kindly suggested to try a similar example in my
computer at home accessing the yahoo.com site and it worked perfectly so I'm
really stuck with this!!

Any comments or suggestions are appreciated!!

ML