I need to run a report on a web page and import it to excel in an automatic
way.

First I need to open this company 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
<option value="763"1EDCMAT x unidad</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.frmRepo rt.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.frmRepo rt.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 to import
this directly into any users excel. But I absolutely have no idea where to
start to make this work. I read thru some of the other posts but they are
very specific to a given web page. That's why I included the source code.

Any comments or suggestions are more than welcome!

Thanks, Martin L.

This might get you started or at least point into the right direction.
If i read your source code correctly this should:
1. open up the site
2. select report
3. select date (make sure specify the format in the code as per my
comment in there)
4. click on submit button (make sure to find its ID in your source
code and amend my code accordingly - as per my comment).
I didn't go any further than that - i.e., i suggest you get this thing
working this far and then proceed to the 'import' section.

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

'Check this out: http://www.mrexcel.com/forum/showthread.php?t=302438

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"

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

End With

Set varHtml = ie.Document

With varHtml
.getElementById("lblSelectReport").Value = 763 'I think 763
stands for 1EDCMAT
.getElementById("lblInitiateDt").Value = Format(Now, "yyyy-mm-
dd") 'Replace this "yyyy-mm-dd" with the format that your site uses.
.getElementById("YourSubmitButtonIDGoesHERE").Clic k 'Amend as
appropriete - find the id of your button and put in here

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

End Sub

---------
I found this info in order to reference the shdocvw object, but the
"Microsoft Internet Controls." option doesn't appear in the dialog box. Is
there another way to reference it? And I only have limited internet/intranet
access here at work so I can't access the "mrexcel.com/forum".

"The References dialog box lists all the objects available to Visual Basic.
You access the dialog box by selecting Tools/References from the menu bar.
Figure 3-5 shows the References dialog box with a reference set to the
Internet Explorer object library. The proper reference is described in the
dialog box as "Microsoft Internet Controls." "

It might also be called something along 'Microsoft Browser
Helper' (it's an interesting one as once you reference it - it would
change to 'Microsoft Internet Controls' anyway.

You can try using late binding instead and declare it object but it's
way easier to get the refference as you'd need to amend the code to
drop constants and replace the =New with Create Object. So, try
finidng the Browser Helper refference in the first place.


On Mar 25, 2:49*pm, MartinL wrote:
---------
I found this info in order to reference the shdocvw object, but the
"Microsoft Internet Controls." option doesn't appear in the dialog box. Is
there another way to reference it? And I only have limited internet/intranet
access here at work so I can't access the "mrexcel.com/forum".

"The References dialog box lists all the objects available to Visual Basic.
You access the dialog box by selecting Tools/References from the menu bar..
Figure 3-5 shows the References dialog box with a reference set to the
Internet Explorer object library. The proper reference is described in the
dialog box as "Microsoft Internet Controls." "

Yes I found the "Microsoft Browser Helper" and now it compiles correctly.
But now I am getting this error: "Object variable or With block variable not
set (Error 91)" upon executing: .getElementById("lblSelectReport").Value = 762

Do you know what this may be? . . .

It means that it's unable to get the lblSelectReport element from the
HTML.
Try replacing it (lblSelectReport) with:
selSavedReports

It's that i'm trying to guess which element exactly from the HTML
holds the valu of the report you want to run. Without having access to
the exact working website it's almost hit&miss.

Also, depending on the design of the web page it might not be yet
loaded before the vba tries to grab the element (i.e., this
'wait until IE finished loading the page
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

might still not determine precisely if the web page has actually
loaded completely.
To check this put 'Stop' before this
Set varHtml = ie.Document
in the vba code, so it would look something like this:
......
End With

Stop ' This will halt your vba code here so that you could
manually chekc if the Internet Explorer actually _
has loaded completely. Then to proceed hit either F5 to
run the whole code or F8 to step it through step by step.
Set varHtml = ie.Document


With varHtml
.........



On Mar 25, 4:12*pm, MartinL wrote:
Yes I found the "Microsoft Browser Helper" and now it compiles correctly.
But now I am getting this error: "Object variable or With block variable not
set (Error 91)" upon executing: .getElementById("lblSelectReport").Value = 762

Do you know what this may be? . . .