CODEWITHSUNDEEP
htmlvbaweb
FrostE1
FrostE1

Reputation: 41

VBA to open webpage then submit data

I am very new to IE controlled by vba, but have stumbled by to write the following code, which was pieced together using various online searches.

My issue is that while this code works if a breakpoint is added before the 'click/submit' is clicked (then going to the IE window), it does not seem to update the webpage when it is run without a breakpoint.

If I were to break the code and see where the cursor is on the webpage, then resume the code, it will save and update but i can't seem to get it working normally.

Any help is much appreciated.

Sub test()
url = "Sharepoint - hidden"

Set IE = New InternetExplorerMedium
    IE.Visible = True
    IE.Navigate url
    
    
Dim HTMLDOC As HTMLDocument

Dim MyHTML_Element As IHTMLElement
Do
Loop Until IE.ReadyState = READYSTATE_COMPLETE

Set HTMLDOC = IE.Document


HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5").innerText = "New Employee - Test:" & Now()   '[Request Type]
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5").Focus
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_B3").Click
End sub

Upvotes: 1

Views: 594

Answers (3)

FrostE1
FrostE1

Reputation: 41

After a few days trying all sorts of solutions, I have found one that works and it is as simple as adding in a couple more .Focus lines as follows:

HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5").Focus
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5").innerText = "New Employee - Test:" & Now()   '[Request Type]
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5").Focus
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_B3").Focus
HTMLDOC.all.Item("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_B3").Click

Upvotes: 1

DimRasRecordset
DimRasRecordset

Reputation: 66

Sorry, I don't have enough reputation yet to post comments. You need to slow down when doing automation. Try using my Pause() subroutine after every line interacting with the HTMLDOC. Start with 2 seconds and reduce gradually while testing for reliability. Something like the following (not tested).

A better way to automate browsers with VBA is with Selenium. I recommend watching this excellent tutorial by Gove Allen.

Sub test()
   Const SPEED = 2.0 'reduce 
   url = "Sharepoint - hidden"

   Set IE = New InternetExplorerMedium
   IE.Visible = True: Pause SPEED
   IE.Navigate url: Pause 5
    
    
   Dim HTMLDOC As HTMLDocument

   Dim MyHTML_Element As IHTMLElement
   Do
   Loop Until IE.ReadyState = READYSTATE_COMPLETE

   Set HTMLDOC = IE.Document

   Dim sCntrl1 as string: sCntrl1 = "ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_T5"
   Dim sCntrl2 as string: sCntrl2 = "ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_B3"
   HTMLDOC.all.Item(sCntrl1).innerText = "New Employee - Test:" & Now() : Pause SPEED
   HTMLDOC.all.Item(sCntrl1).Focus : Pause SPEED
   HTMLDOC.all.Item(sCntrl2).Click : Pause SPEED
End sub

Upvotes: 0

Scott Ridings
Scott Ridings

Reputation: 844

You should be able to use:

IE.document.getElementById("ID_Of_Your_Element").Click

If your case, you can use:

HTMLDOC.getElementById("ctl00_ctl40_g_07433f93_b974_4a29_9e53_cc3a087aa20a_FormControl0_V1_I1_B3").Click

Upvotes: 0

Related Questions