run report on web page, import to excel

[MartinL]

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

 

[AB]

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").Click '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

 

[MartinL]

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

 

[AB]

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.

 

[MartinL]

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

 

[AB]

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

 

[AB]

one you get past the 'select report' thing you might get similar
compile error on the date selection, if so - try replacing that one
lblInitiateDt
with
txtInitiateDtFrom

It seems that your web developer used the 'lbl' to specify LABELS and
TXT to specify text fields and SEL to specify select fields (which
makes sense) - error in my initial code was that i was trying to set a
value of a label (lblSe...) which can't be done. The same applies to
the date field - i was trying to set a value to lblIn... (a label) -
therefore try the txtInitiateDtFrom.
The same might apply to the DateTo (tha'ts not in my code at all yet)
- for that one the id seems to be:
txtInitiateDtTo

AB.

 

[MartinL]

one you get past the 'select report' thing you might get similar
compile error on the date selection, if so - try replacing that one
lblInitiateDt
with
txtInitiateDtFrom

It seems that your web developer used the 'lbl' to specify LABELS and
TXT to specify text fields and SEL to specify select fields (which
makes sense) - error in my initial code was that i was trying to set a
value of a label (lblSe...) which can't be done. The same applies to
the date field - i was trying to set a value to lblIn... (a label) -
therefore try the txtInitiateDtFrom.
The same might apply to the DateTo (tha'ts not in my code at all yet)
- for that one the id seems to be:
txtInitiateDtTo

AB.

I realy appreciate the time you are taking to help me out on this, but I
can't seem to get past the "lblSelectReport",. Question: So if I take away
the "lbl" part it should work? . . . because I tried and it doesn't . ..
maybe it would help if I emailed you the complete source code and it could
save some time. . . please let me know and thanks once again.

I tried it these 3 ways, I change to 932 because that's the report I really
need. But they all send the same error message: Run-time error 91

..getElementById("lblSelectReport").Value = 932
..getElementById("SelectReport").Value = 932
..getElementById("SelectReport").Value = 932

 

[AB]

No worries - that's what people are here for! I just hope i'll be able
to help.

try this (from your post i gather you didn't try the one that starts
with 'sel'):

..getElementById("selSavedReports").Value = 932


The 'lblSelectReport' was completely wrong element in the html - my
bad - i picked a lable element instead of an element that can actually
be changed (like text box or select drop down etc.) - taking away par
to element's name would just mean that code won't be able to find it.
The reference to 'lbl' was just me trying to explain how to see those
elements in the html by yourself.

Try putting (before the line that fires error) this:
msgbox "I managed to read the label. It says: "
& .getElementById("lblSelectReport").InnerText
or this:
msgbox "I managed to read the label. It says: "
& .getElementById("lblSelectReport").InnerHtml

If this message fires and it has read the label text then that would
demonstrate how the vba code can read the elements.

Post back how you get on.

 

[MartinL]

Try putting (before the line that fires error) this:

msgbox "I managed to read the label. It says: "
& .getElementById("lblSelectReport").InnerText
or this:
msgbox "I managed to read the label. It says: "
& .getElementById("lblSelectReport").InnerHtml

If this message fires and it has read the label text then that would
demonstrate how the vba code can read the elements.

I tried including the msgbox both ways but I keep getting the same error.
I'm cincluding the complete code just in case I'm missing something else:

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
'MsgBox "I managed to read the label. It says: " _
& .getElementById("lblSelectReport").innerText

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


..getElementById("selSavedReports").Value = 932
'.getElementById("selSelectReport").Value = 932
'.getElementById("lblManageReports").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

 

[MartinL]

I'm also including the first few lines of the source code of the web page.
Maybe there's something there that could give you a clue. . . .


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title id="htmTitle">Defect List Report</title>
<base target="_self">
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
<LINK href="/defaultscreen.css" type="text/css" rel="stylesheet">
<script language="javascript">
//**************************************************************
//Pop up window when Submit button is click
//**************************************************************
//
function UnloadWin(){
}

 

[AB]

If you comment out the msgbox does this:

..getElementById("selSavedReports").Value = 932

still fire the run-time error?

 

[AB]

if this:

If you comment out the msgbox does this:

.getElementById("selSavedReports").Value = 932

still fire the run-time error?

still doesn't work and because i'm running out of ideas where i'm
getting it wrong - i thought i'd demonstrate how the whole automate-ie-
from-vba thing works on a web site that we both should have access to
- www.yahoo.com and hopefully it would help you fix the code and see
what's wrong with it as i currently struggle to figure that out.

So, this is pretty much the same code as for your site (the structure
and principles) just it's a public url.

Sub Test()

'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
.Navigate "www.yahoo.com" 'Check out Yahoo site

'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("p_13838465-p").Value = "aaa" 'Yahoo has coded
the search box _
to have an ID of 'p_13838465-p' - so grab it and set
its value to aaa
MsgBox "Check out the Browser - it should be on Yahoo and the
search box " _
& "should say 'aaa'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementsByName("p").Item.Value = "bbb" 'The exact saem
element (yahoo search _
box) has not only ID of p_13838465-p but also a name
of "p" - so, you _
can refference the element not only by ID but also by
its name. So this _
one changes the search box to bbb

MsgBox "This time the search box " _
& "should say 'bbb'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementById("search-submit").Click 'The yahoo page
'Search' button has id of _
'search-submit' and so you can get the element by the
id and click on it _
that's what this line of code does.

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

MsgBox "Check out the Browser - now it should have searched
for " _
& "'bbb'", vbOKOnly

End With

End Sub

 

[MartinL]

Yes it does.

I was reading something about the .getElementById function that said that it
could be in conflict with another defined function with the same name, in
some other library, coul it be that? . . or could I redefine this function in
another way? . . .

 

[MartinL]

if this:


still doesn't work and because i'm running out of ideas where i'm
getting it wrong - i thought i'd demonstrate how the whole automate-ie-
from-vba thing works on a web site that we both should have access to
- www.yahoo.com and hopefully it would help you fix the code and see
what's wrong with it as i currently struggle to figure that out.

So, this is pretty much the same code as for your site (the structure
and principles) just it's a public url.

Sub Test()

'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
.Navigate "www.yahoo.com" 'Check out Yahoo site

'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("p_13838465-p").Value = "aaa" 'Yahoo has coded
the search box _
to have an ID of 'p_13838465-p' - so grab it and set
its value to aaa
MsgBox "Check out the Browser - it should be on Yahoo and the
search box " _
& "should say 'aaa'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementsByName("p").Item.Value = "bbb" 'The exact saem
element (yahoo search _
box) has not only ID of p_13838465-p but also a name
of "p" - so, you _
can refference the element not only by ID but also by
its name. So this _
one changes the search box to bbb

MsgBox "This time the search box " _
& "should say 'bbb'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementById("search-submit").Click 'The yahoo page
'Search' button has id of _
'search-submit' and so you can get the element by the
id and click on it _
that's what this line of code does.

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

MsgBox "Check out the Browser - now it should have searched
for " _
& "'bbb'", vbOKOnly

End With

End Sub
.

I will have to try the Yahoo example at home, because I only have limited
internet access here at work. I was thinking that maybe it could be a
security issue because this is an internal company web site that may somehow
restrict this kind of access from VBA.

 

[AB]

I've never had that before but it doesn't mean that It's not the
problem.
Try out the Yahoo sample at home - this should give you a good
understading how it works (well, you should finally see that it
actually does work ).

One more suggestion, though, - maybe still try at the office this

..getElementById("selSavedReports").Value = 763'I know it's not the
report you want but the first option looks somehat _
unconvincing to me in the source code.

or maybe even this:

..getElementById("selSavedReports").Value = "763"'ie. string instead of
Long

as the goal at this point is just to finally get any element changed
by vba on that site of yours.

 

[MartinL]

AB I will try this and let you know how it went, I'm pretty sure it's may be
something related to security, this is pretty straight forward.

..getElementById("selSavedReports").Value = 763

..getElementById("selSavedReports").Value = "763"

I tried both but still got the same message, sorry.

 

[MartinL]

if this:


still doesn't work and because i'm running out of ideas where i'm
getting it wrong - i thought i'd demonstrate how the whole automate-ie-
from-vba thing works on a web site that we both should have access to
- www.yahoo.com and hopefully it would help you fix the code and see
what's wrong with it as i currently struggle to figure that out.

So, this is pretty much the same code as for your site (the structure
and principles) just it's a public url.

Sub Test()

'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
.Navigate "www.yahoo.com" 'Check out Yahoo site

'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("p_13838465-p").Value = "aaa" 'Yahoo has coded
the search box _
to have an ID of 'p_13838465-p' - so grab it and set
its value to aaa
MsgBox "Check out the Browser - it should be on Yahoo and the
search box " _
& "should say 'aaa'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementsByName("p").Item.Value = "bbb" 'The exact saem
element (yahoo search _
box) has not only ID of p_13838465-p but also a name
of "p" - so, you _
can refference the element not only by ID but also by
its name. So this _
one changes the search box to bbb

MsgBox "This time the search box " _
& "should say 'bbb'" & vbCrLf & "Click OK only once you've
checked!", vbOKOnly

.getElementById("search-submit").Click 'The yahoo page
'Search' button has id of _
'search-submit' and so you can get the element by the
id and click on it _
that's what this line of code does.

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

MsgBox "Check out the Browser - now it should have searched
for " _
& "'bbb'", vbOKOnly

End With

End Sub
.

 

[MartinL]

Tried this at home, it works perfectly!!!!! why doesn't it work at work??

 

[AB]

Sorry Martin, it seems that I didn't manage to help you after all -
I've been automating ie only for last couple of months myself and so
i've run out of suggestions.
The only last bit up my sleeve - I've had it myself when IE was always
creating a new instance whenever i .navigate to a specific URL,
meaning - when the code runs and creates the IE object, makes it
visible - i've got 1 IE window on my screen but then I .navigate the
the site i needed and that IE was (for some reason) creating new
window for that site and obviously my code wasn't controlling the new
window (but only the old) and therefore code couldn't find the
elements i wanted it to change (as the old window didn't pull the web
site - the new did). It was IE7 - so what I ended up doing was
installed IE8 and it did the trick (it stopped creating the new
windows).
There is a method to catch the .newwindow or something like that in
the shdoc - you can try googling for it.
So, this seems to be the last thing i had that could be it (i.e.,
maybe your ie also was creating another window) but if not - i'm
afraid you'll need to open up another thread or ask someone else.
I hope you make it work!

A.