Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Stuck on VBA Web Automation
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Stuck on VBA Web Automation
Hi Folks

I am having trouble trying to get VBA to click on an image on a web page.
The HTML java code is as follows: (sendsmsform is a function)

<!--<input type="button" name="submitsms" value="send your message"
onClick="sendsmsform();"--
<a href="#" onClick="sendsmsform();"<img src="/messaging/send_message.jpg"
alt="send message" border="0" /</a

My VBA code is as follows:

Private Sub SMS_STORE_FIGURES_TO_MANAGERS_2()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


Application.StatusBar = "Search form submission. Please wait..."

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Number" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "Neil is SOOOO COOOOL"

End If
i = i + 1
Wend

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "readit" Then

' Set text for search
Set objElement = objCollection(i)
objElement.Click
End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("img")

' Set text for search
Set objElement = objCollection

objElement.onclick


' Wait while IE re-loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""
End Sub

If you need the Full Code for the web page I can add this although its a
company internal page so you will not be able to view it.

--
Nelly
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Stuck on VBA Web Automation
I think the answer "MAY" be simple. Yo need to select the button

objElement.select
objElement.onclick

You may have multiple tag items named "img".

You may be able to get the button name with this line
set obj = IE.Document.all.item("submitsms")


this debug code may help

Private Sub Dump()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
for each itm in IE.Document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.id 'comment out if errors
Range("D" & RowCount) = itm.name 'comment out if errors
Range("E" & RowCount) = left(itm.innertext,1024)
RowCount = Rowcount + 1
next itm

end Sub

"nelly" wrote:

Hi Folks

I am having trouble trying to get VBA to click on an image on a web page.
The HTML java code is as follows: (sendsmsform is a function)

<!--<input type="button" name="submitsms" value="send your message"
onClick="sendsmsform();"--
<a href="#" onClick="sendsmsform();"<img src="/messaging/send_message.jpg"
alt="send message" border="0" /</a

My VBA code is as follows:

Private Sub SMS_STORE_FIGURES_TO_MANAGERS_2()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


Application.StatusBar = "Search form submission. Please wait..."

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Number" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "Neil is SOOOO COOOOL"

End If
i = i + 1
Wend

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "readit" Then

' Set text for search
Set objElement = objCollection(i)
objElement.Click
End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("img")

' Set text for search
Set objElement = objCollection

objElement.onclick


' Wait while IE re-loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""
End Sub

If you need the Full Code for the web page I can add this although its a
company internal page so you will not be able to view it.

--
Nelly
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Stuck on VBA Web Automation
Hi Joel

set obj = IE.Document.all.item("submitsms") - obj = Nothing

A runtime error occurs on objElement.select

Regards
--
Nelly


"Joel" wrote:

I think the answer "MAY" be simple. Yo need to select the button

objElement.select
objElement.onclick

You may have multiple tag items named "img".

You may be able to get the button name with this line
set obj = IE.Document.all.item("submitsms")


this debug code may help

Private Sub Dump()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
for each itm in IE.Document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.id 'comment out if errors
Range("D" & RowCount) = itm.name 'comment out if errors
Range("E" & RowCount) = left(itm.innertext,1024)
RowCount = Rowcount + 1
next itm

end Sub

"nelly" wrote:

Hi Folks

I am having trouble trying to get VBA to click on an image on a web page.
The HTML java code is as follows: (sendsmsform is a function)

<!--<input type="button" name="submitsms" value="send your message"
onClick="sendsmsform();"--
<a href="#" onClick="sendsmsform();"<img src="/messaging/send_message.jpg"
alt="send message" border="0" /</a

My VBA code is as follows:

Private Sub SMS_STORE_FIGURES_TO_MANAGERS_2()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


Application.StatusBar = "Search form submission. Please wait..."

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Number" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "Neil is SOOOO COOOOL"

End If
i = i + 1
Wend

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "readit" Then

' Set text for search
Set objElement = objCollection(i)
objElement.Click
End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("img")

' Set text for search
Set objElement = objCollection

objElement.onclick


' Wait while IE re-loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""
End Sub

If you need the Full Code for the web page I can add this although its a
company internal page so you will not be able to view it.

--
Nelly
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Stuck on VBA Web Automation
The all method I jsut learned about and haven';t fully learned how to use.
It seem to find object listed as ID= but not on tags. If yo are getting an
error on the select then you are not on an object the has an ONClick property.

You need to run the dump program I posted and post the rows from the excel
sheet near the object you are trying to find.

I sometime have to use code like this to stop . then set a wat item to
"ITM" and look through the property until you find an item with an ONCLICK
property. It is sometimes one ro two objects away from the item that yo are
looking for. they also may be children of the object you are looking for. I
sometime get very frustrated trying to get code like this working, but I
always find a way.

Private Sub DSopp()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
For Each itm In IE.Document.all
If RowCount = 135 Then Stop

RowCount = RowCount + 1
Next itm

End Sub


"nelly" wrote:

Hi Joel

set obj = IE.Document.all.item("submitsms") - obj = Nothing

A runtime error occurs on objElement.select

Regards
--
Nelly


"Joel" wrote:

I think the answer "MAY" be simple. Yo need to select the button

objElement.select
objElement.onclick

You may have multiple tag items named "img".

You may be able to get the button name with this line
set obj = IE.Document.all.item("submitsms")


this debug code may help

Private Sub Dump()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
for each itm in IE.Document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.id 'comment out if errors
Range("D" & RowCount) = itm.name 'comment out if errors
Range("E" & RowCount) = left(itm.innertext,1024)
RowCount = Rowcount + 1
next itm

end Sub

"nelly" wrote:

Hi Folks

I am having trouble trying to get VBA to click on an image on a web page.
The HTML java code is as follows: (sendsmsform is a function)

<!--<input type="button" name="submitsms" value="send your message"
onClick="sendsmsform();"--
<a href="#" onClick="sendsmsform();"<img src="/messaging/send_message.jpg"
alt="send message" border="0" /</a

My VBA code is as follows:

Private Sub SMS_STORE_FIGURES_TO_MANAGERS_2()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


Application.StatusBar = "Search form submission. Please wait..."

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Number" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "Neil is SOOOO COOOOL"

End If
i = i + 1
Wend

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "readit" Then

' Set text for search
Set objElement = objCollection(i)
objElement.Click
End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("img")

' Set text for search
Set objElement = objCollection

objElement.onclick


' Wait while IE re-loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""
End Sub

If you need the Full Code for the web page I can add this although its a
company internal page so you will not be able to view it.

--
Nelly
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Stuck on VBA Web Automation
Hi Jeol

Many thanks for your ideas. Through trial and error from the the info you
gave I got this to work by the following.

Set obj = IE.Document.all.Item("smsform")

obj.submit

Thanks Again
--
Nelly


"Joel" wrote:

The all method I jsut learned about and haven';t fully learned how to use.
It seem to find object listed as ID= but not on tags. If yo are getting an
error on the select then you are not on an object the has an ONClick property.

You need to run the dump program I posted and post the rows from the excel
sheet near the object you are trying to find.

I sometime have to use code like this to stop . then set a wat item to
"ITM" and look through the property until you find an item with an ONCLICK
property. It is sometimes one ro two objects away from the item that yo are
looking for. they also may be children of the object you are looking for. I
sometime get very frustrated trying to get code like this working, but I
always find a way.

Private Sub DSopp()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
For Each itm In IE.Document.all
If RowCount = 135 Then Stop

RowCount = RowCount + 1
Next itm

End Sub


"nelly" wrote:

Hi Joel

set obj = IE.Document.all.item("submitsms") - obj = Nothing

A runtime error occurs on objElement.select

Regards
--
Nelly


"Joel" wrote:

I think the answer "MAY" be simple. Yo need to select the button

objElement.select
objElement.onclick

You may have multiple tag items named "img".

You may be able to get the button name with this line
set obj = IE.Document.all.item("submitsms")


this debug code may help

Private Sub Dump()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


RowCount = 1
for each itm in IE.Document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.id 'comment out if errors
Range("D" & RowCount) = itm.name 'comment out if errors
Range("E" & RowCount) = left(itm.innertext,1024)
RowCount = Rowcount + 1
next itm

end Sub

"nelly" wrote:

Hi Folks

I am having trouble trying to get VBA to click on an image on a web page.
The HTML java code is as follows: (sendsmsform is a function)

<!--<input type="button" name="submitsms" value="send your message"
onClick="sendsmsform();"--
<a href="#" onClick="sendsmsform();"<img src="/messaging/send_message.jpg"
alt="send message" border="0" /</a

My VBA code is as follows:

Private Sub SMS_STORE_FIGURES_TO_MANAGERS_2()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate "http://intranet/cgi-bin/sms.pl?trig=template"

' Statusbar
Application.StatusBar = "messaging is loading. Please wait..."

' Wait while IE loading...
'IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.Busy = True
DoEvents
Loop


Application.StatusBar = "Search form submission. Please wait..."

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Number" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "07971411105"

End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("textarea")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "Message" Then

' Set text for search
objCollection(i).Value = "Neil is SOOOO COOOOL"

End If
i = i + 1
Wend

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

i = 0
While i < objCollection.Length
If objCollection(i).Name = "readit" Then

' Set text for search
Set objElement = objCollection(i)
objElement.Click
End If
i = i + 1
Wend

Set objCollection = IE.Document.getElementsByTagName("img")

' Set text for search
Set objElement = objCollection

objElement.onclick


' Wait while IE re-loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Show IE
IE.Visible = True

' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""
End Sub

If you need the Full Code for the web page I can add this although its a
company internal page so you will not be able to view it.

--
Nelly
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorry but i am stuck again KODIAK BEAR New Users to Excel 5 October 1st 07 08:46 PM
Supressing the ctrl-c and other keys during word automation in automation apondu Excel Programming 0 July 19th 07 10:10 PM
Help, please, I'm stuck Harvest Excel Discussion (Misc queries) 3 August 19th 06 03:28 AM
Stuck PaulOakley[_2_] Excel Programming 13 July 13th 05 05:54 PM
stuck please help derekc[_8_] Excel Programming 2 May 19th 04 06:08 AM


All times are GMT +1. The time now is 03:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement