Click on button using VBA

[muthoosmathew]

Guys i need to Click on web page button using Excel Macro.

the source is as follows

<button type="button" class="button" onMouseOver="this.className='buttonOn'" onMouseOut="this.className='button'" onClick="submitForm('search.basicSearchForm','search.basicSearchValidate');">Search</button>

My code is

Set objCollection = IE.document.getElementsByTagName("button")

i = 0

While i < objCollection.Length
If objCollection(i).onClick="submitForm('search.basicSearchForm','search.basicSearchValidate');" Then

Set objElement = objCollection(i)

End If
i = i + 1
Wend


objElement.Click


This is not working..
Please give me ideas(simple though as i am a beginner in this)


Mats.

 

[ron]

Can we access the webpage? If so, give us the url...Ron

 

[Mats]

Thanks Ron for the reply.
Unfortunately the site can only be accessed through my company intranet due to security reasons.

Can you suggest any other options.

Mats

 

[Walter Briscoe]

In message <[email protected]> of
Fri, 23 Aug 2013 02:29:35 in microsoft.public.excel.programming,
(e-mail address removed) writes

Guys i need to Click on web page button using Excel Macro.

the source is as follows

<button type="button" class="button" onMouseOver="this.className='butto
nOn'" onMouseOut="this.className='button'" onClick="submitForm('search.
basicSearchForm','search.basicSearchValidate');">Search</button>

My code is

Set objCollection = IE.document.getElementsByTagName("button")

i = 0

While i < objCollection.Length
If objCollection(i).onClick="submitForm('search.basicSearchForm
','search.basicSearchValidate');" Then

Set objElement = objCollection(i)

End If
i = i + 1
Wend


objElement.Click


This is not working..
Please give me ideas(simple though as i am a beginner in this)


Mats.

What do you mean by "This is not working.."? Either the matching code
does not find the object you want or objElement.Click does not do what
you want.

Have you stepped through the code in the VBA debugger?
If not, please do so and tell us what you find. Your code finds the last
match if there is more than one. I think that is probably not what you
want. There may be a second criterion.
Your code is incomplete. You might post a standalone module, starting
Option Explicit

Is there a public site which has a similar structure, where your code
can be tested?

I assume objElement is an HTMLInputElement. Typename is a useful
function for narrowing the type of a variable.

I find it hard to drive Internet Explorer from Excel. It repays the
effort.
I have had some use from DebugBar from <http://http://www.debugbar.com/>
It has a control "Drag document on target to find element".
Having that, I can step through the node tree with something horrible
like CurTime = GetBranch(Doc, 0, 1, 1, 1, 4, 0, 18).Item(0).data

where GetBranch is the following utility function.

Public Function GetBranch(ByVal O As Object, ParamArray Path() As Variant) As Object
' Doc is HTMLDocument, Doc.Body is HTMLBody
Dim T As Object
Dim V As Variant

Set T = O.childNodes
For Each V In Path
' Debug.Print "V = " & V, "T.Length = " & T.Length, & _
"TypeName(T.Item(V)) = " & TypeName(T.Item(V))
' On Error Resume Next: DebugPrint "t.item(" & V & ").innertext = """ & T.Item(V).innerText & """": On Error GoTo 0
Set T = T.Item(V).childNodes
Next V
Set GetBranch = T
End Function

I can't drive every site, but can drive many.

I have failed to analyse
<http://www.tfl.gov.uk/livetravelnews/planned-works/calendar/default.aspx>.
Your posting prompts me to try again.

 

[Mats]

Yes Walter, i have stepped through the code and as you said the error i am getting is 'object variable or block variable not set'.

I tried in other sites where the button is defined as the <input> tag.

there i used the following code.

Set objCollection = IE.document.getElementsByTagName("input")

i = 0

While i < objCollection.Length
If objCollection(i).type = "submit" And _
objCollection(i).name = "button name" Then

Set objElement = objCollection(i)

End If
i = i + 1
Wend


objElement.Click

Here i was able to get the result.

But if the site is using <button> tag, how to do it?

And i am really not a programmer. I have a basic knowledge in prgramming. What i am trying to do is to automate a set of process which i have to do every month for some of my reports i have to prepare which are having the same set of process.

Your help will be highly appreciated.

 

[Walter Briscoe]

In message <[email protected]> of
Mon, 26 Aug 2013 04:12:07 in microsoft.public.excel.programming, Mats

Yes Walter, i have stepped through the code and as you said the error i
am getting is 'object variable or block variable not set'.

I don't think I said that. Because you do not quote me, I am unsure.
I infer that your button finding code is the bit which does not work.

I have made huge progress with the site I said I could not analyse.
Your question really motivated me.

I quote your original posting so I can interleave comments.

Set objCollection = IE.document.getElementsByTagName("button")

i = 0

While i < objCollection.Length

Does objCollection.Length have the expected value?
In the debugger, use Ctrl+G to open the immediate debug pane.
In that pane, type ?objCollection.Length or
debug.print objCollection.Length

At this point, I would be inclined to insert a line of code (untested)
like
debug.print "objCollection(" & i & ").onClick = """" & objCollection(i).onClick & """": stop

If objCollection(i).onClick="submitForm('search.basicSearchForm>','search.basicSearchValidate');" Then

Set objElement = objCollection(i)

I think you need an exit while here, but think that does not exist.
for i = 0; i < objCollection.Length; i = i+1
If objCollection(i).onClick = _
"submitForm('search.basicSearchForm>'," & _
'search.basicSearhValidate');" then
Set objElement = objCollection(i)
exit for ' quick exit on 1st match
end if
next i
debug.assert i < objCollection.Length ' Check we matched

End If
i = i + 1
Wend


objElement.Click

I asked several questions. I see no answers.
When you reply, start by quoting this posting. If you want to exclude
parts of it, use something like a line consisting of
[snip]

I tried in other sites where the button is defined as the <input> tag.

there i used the following code.

Set objCollection = IE.document.getElementsByTagName("input")

i = 0

While i < objCollection.Length
If objCollection(i).type = "submit" And _
objCollection(i).name = "button name" Then

Set objElement = objCollection(i)

End If
i = i + 1
Wend


objElement.Click

Here i was able to get the result.

But if the site is using <button> tag, how to do it?

And i am really not a programmer. I have a basic knowledge in

I assume you aim to automate a boring task.
That is the main purpose of programming for me.

 

[GS]

What i am trying to do is to automate a set of process which i have

to do every month for some of my reports i have to prepare which are
having the same set of process.

<FWIW>
I do similar except I pass a delimited string in the URL and use
JavaScript to parse the data to the appropriate fields/controls on the
webpage. I leave the 'submit' action up to the user.

I suspect, though, that you're trying to upload report data which IMO
should be done via a file that others can download to display the
report. All this can be automated with VBA so it's 1 click to upload, 1
click to download/display. I guess it's just a matter of personal
preference how you want to approach the task. Personally, I prefer a
'no nonsense, get-the-job-done' approach that everyone using the
utility understands. This, of course, requires making a macro available
to all users who will be using the report! In the case of sensitive
data, the file doesn't need to be downloaded to disk to be
opened/displayed, actually, and any temp files used by your process can
be deleted when the report is displayed OR its window closes.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion