+ Reply to Thread
Results 1 to 13 of 13

Use VBA to update a Webpage using JavaScript

Hybrid View

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Use VBA to update a Webpage using JavaScript

    Hi,
    I'm trying to use excel to update a webpage for work. It's a shared webpage that is basically a Checklist that the owner of that task will fill in confirming that their part of the process is complete. Once this is done by the owner, the supervisor of the owner's group would need to go in and click the Radio Button to either Approve or Reject. There are a ton of Radio Buttons that need to be clicked, so I was able to get this part to work, but for ALL radio buttons.


    I'm sure (nay, certain) below is the long way of doing it, but this part of the code has been working, and just clicks approve on all of the radio buttons.


    Sub Checklist()
    
    Set oIE = CreateObject("InternetExplorer.Application")
        
        For Each oIE In CreateObject("shell.Application").Windows
            If InStr(1, oIE.LocationURL, "internal website here") > 0 Then
            'If InStr(1, oIE.LocationURL, "internal website here") > 0 Then
                    oIE.Visible = True
                    
        On Error Resume Next
        
        oIE.Document.getElementsByName("ctl00$ContentPlaceHolder1$dgReports$ctl02$gnVerify").Item(0).Click
    
        oIE.Document.getElementsByName("ctl00$ContentPlaceHolder1$dgReports$ctl03$gnVerify").Item(0).Click
        
        oIE.Document.getElementsByName("ctl00$ContentPlaceHolder1$dgReports$ctl04$gnVerify").Item(0).Click
    
        oIE.Document.getElementsByName("ctl00$ContentPlaceHolder1$dgReports$ctl05$gnVerify").Item(0).Click
    
    Etc...
    My problem now is that I only would like to have the macro click Approve when someone from my group completes the job. So, say there are 10 steps in the process. Steps 1-5 are done by Tim, steps 6-8 are done by Joe and steps 9 & 10 are done by Mike.

    Is there a way that I can get the code to only click the radio buttons for just Tim? I thought maybe an If statement would work, but I can't get it to work. I didn't get very far with it, but it was still Appproving all of them or just skipping all of the radio buttons.

    When I look at the JavaScript, this is the part where I see the name of the owner who completed the task.

    <span id="ctl00_ContentPlaceHolder1_dgReports_ctl03_lbAuditValue1Name">Tim</span>

    Thanks in advance for your help!

    Moderator Note:

    Pls use code tags around your code next time as per forum rules.
    Last edited by Fotis1991; 10-11-2013 at 10:39 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    That isn't JavaScipt it's and HTML span tag.

    Where are the option buttons for each person located in relation to their tag?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    Oh, sorry! Thought it was Java.
    The user has a login ID and PW. So we can identify the person updating each step.
    They go through the steps '1-10', and it could be a radio button for a simple yes/no, or a drop down menu with a list of options. They click submit and it gets saved. That's when this piece would show up in the code.
    <span id="ctl00_ContentPlaceHolder1_dgReports_ctl03_lbAuditValue1Name">Tim</span>
    Thanks,

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    Do you mean that which option buttons appear depends on who has logged on?

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    Nope. No matter who logs in, all the same options will be there for them. They just fill in the steps they completed. Once they submit the form, the supervisor will now see two radio buttons; Approve or Reject.

    The code from my original post (and this below) can go through all the radio buttons and choose Approve.
    oIE.Document.getElementsByName("ctl00$ContentPlaceHolder1$dgReports$ctl03$gnVerify").Item(0).Click
    I would just like to modify this bit of code to click only the radio buttons for certain employees, rather than all of them.

    Is there a way to have VBA search through the code from the website and only approve when it's submitted by Tim?
    HTML Code: 
    Thank you,

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    How can it be determined which option buttons should be clicked for each person?

    Do you have a list?

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    Sorry if I'm not explaining it correctly.
    I'm only interested in automating the steps after the employee has submitted their part of the checklist. When they do, the radio buttons Approve/Reject are now active. I would like to click Approve on the steps that was completed by a certain person.

    Thanks,

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    So how do you determine which steps those are?

  9. #9
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    I was hoping that the macro could be adjusted to Find each instance where a step was submitted by the guy on my staff, and click the Approve radio button that corresponds to the step.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    It might be possible, but how are you going to find those instances?

    Is there any pattern, logic?

  11. #11
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    I thought that there might be a way to do an If statement and somehow searching the HTML text. Something to the effect of
    If "ctl00_ContentPlaceHolder1_dgReports_ctl03_lbAuditValue1Name".value = Tim then 'click approve', otherwise 'skip'.
    All the steps are numbered, so sort of 'dgReports_ctl03', 'dgReports_ctl04', 'dgReports_ctl05',, etc. The person who submitted it will be different.

    Thanks,

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Use VBA to update a Webpage using JavaScript

    That's the sort of thing I've been asking about, but since I can't access the page I've no idea what to look for.

  13. #13
    Registered User
    Join Date
    08-14-2012
    Location
    Somerset, NJ
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use VBA to update a Webpage using JavaScript

    Norie, thanks for sticking with me. Hopefully I'll finally get you everything you need to help me out!

    I went to the website and viewed the code. But when submitting it, I get some kind of error when I include it within the post. So I'm going to try to include it a Word Doc and attach it. I copied from the source code, and pasted it into work, so I hope it's in a suitable format.

    Attached is one of the steps that the employees fill in. Since this one was done by Tim, I'd like to have the macro click the approve button. But if it was done by anyone else, have it skip to the next step.

    Hopefully this helps.
    Thanks a lot! I appreciate it.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Anyone know how to read javascript and objects from a webpage
    By Fratshack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2013, 02:08 PM
  2. [SOLVED] Help click on link javascript webpage
    By rickmdz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-06-2012, 11:59 PM
  3. how to download webpage by skipping javascript
    By catjoke in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2010, 12:54 AM
  4. Execute javascript code on a webpage with VBA
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2009, 05:23 AM
  5. [SOLVED] Open a webpage using Javascript in Excel
    By jbrack@email.uophx.edu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-17-2006, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1