VBA webpage navigation

[ianraff]

I'll preface this by saying I'm a total VBA newbie and am fully aware that I'm way in over my head with this, but I'm determined to learn how to do it and finish what I started...

Ultimately, I'm trying to access a table, exportable to excel, in a private database at my company (I want to automate this process). I've managed to launch Internet explorer, utilize a userform to enter username and password, login, navigate to the correct report, but I need the program to select certain categories to generate the report on... In this case they are marketing campaigns and the report is showing the leads generated by those campaigns. Here is my VBA code thus far:

Option Explicit

Sub Conversion()
Dim IE As Object
Dim URL As String
Dim userName As String
Dim password As String
URL = "https://lm.leads360.com"
' Load userform - velocifyLogin

velocifyLogin.Show

' Define variables for login

userName = velocifyLogin.userNameText.Value
password = velocifyLogin.passwordText.Value

' Variables stored. Remove userform

Unload velocifyLogin

' Open Internet Explorer browser
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate URL

' Wait sequence while IE loads...
Do
DoEvents
Loop Until IE.ReadyState = 4
'
'
' Insert Login & Password --> Submit --> Go to Custom Reports
With IE
.document.getelementbyID("usernameTextBox").Value = userName
.document.getelementbyID("passwordTextBox").Value = password
.document.getelementbyID("loginButton").Click
.navigate "https://lm.leads360.com/Web/ReportsCustom.aspx"
End With
'
' Wait sequence while IE loads...
Do
DoEvents
Loop Until IE.ReadyState = 4
'
' Find Conversion Report
With IE
.document.getelementbyID("cph_searchTextBox").Value = "Ian_Automate"
.document.getelementbyID("cph_searchButton").Click
Application.Wait (Now + TimeValue("0:00:02"))
.document.getelementbyID("cph_reportRepeater_runLinkButton_0").Click

End With
End Sub

My problem now is selecting values in a table. HTML?CSS?Java? script is as follows:

table class="datatable"
thead … /thead
tbody
tr class="nohover"
td … /tdtd … /td
td id="cph_filterRepeater_valueCell_0"b /b
select id="cph_filterRepeater_bvc_0" class="idtitlepairlist" multiple="multiple" name="ctl00$cph$filterRepeater$ctl00$bvc" size="4"
option value="132" … /option
option value="28" … /option
option value="89" … /option
etc
etc
etc

I took out the html tags bc I couldn't figure out how to not have it convert into a table.

Within the option value tags are the campaign names.

Bottom line question is... Is there a way to select multiple option values within that table? And if so, is there a way to utilize another userform so that my boss can select the campaigns he's interested in beforehand and the macro just selects and spits out the information after that?

Thanks in advance!

 

[ianraff]

Update - For some reason my Microsoft Internet Controls reference was off... So I turned that on and inserted this line of code:

.document.getelementbyID("cph_filterRepeater_bvc_0").Value = 28

Which, as I change the number, the value in the list box changes... So one step closer... But does anyone know how to select multiple values at one time?

 

[John_w]

Try, inside the With IE clause:

    With .document.getElementById("cph_filterRepeater_bvc_0")
        .Options(0).Selected = True     '1st option
        .Options(3).Selected = True     '3rd option
        'etc.
    End With

If you post HTML code inside HTML tags then the forum won't render it. Like this: [HTML]HTML code here[/HTML]

 

[ianraff]

John_W, thanks man! That's perfect and works like a charm! Here's an add on to that...

Say I have 116 of those options to choose from though. I've designed a userform listbox to appear and I want that to have the functionality of selecting from that list box, which the macro then reads and selects from the webpage. I know that seems unnecessary, but I'm ultimately working towards zero navigation on this macro. Just username and password entry, campaign selection and then what appears to be end sub.

Here's my code so far on the userform:

Public Sub campaignSelect_Initialize()
    campaignselect.ListBox1.RowSource = Range("Campaigns").Address
End Sub

And

Private Sub selectButton_Click()
' Campaigns are stored in hidden worksheet
    Dim campaign As String
    Dim i As Integer
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            campaign = campaign & ListBox1.List(i) & vbNewLine
        End If
    Next i
   MsgBox campaign
   
    campaignselect.Hide
End Sub

That message box in the sub selectButton_click() displays all of the selected campaigns... I'm wondering if there is a way to just simply store those as variables temporarily, then use them in the main module? For example...

Dim obj As Object

    For Each obj In IE.document.all.Item("cph_filterRepeater_bvc_0").Options
        If obj.innerText = '[U][I][B]this is where I would have the campaign array?[/B][/I][/U] Then
            obj.Selected = True
        End If
        
    Next obj

Thoughts? I know it's complicated(maybe it just seems that way?), but this thing will be killer if that's possible.

P.S - Sorry about the HTML tags... It's been forever since I've been a part of a forum and couldn't find the button to enclose them! Thanks for the clarification though - And thanks for your help!

 

[John_w]

Change the campaign string loop to:

campaign = campaign & ListBox1.List(i) & ";"

Then to select each campaign option:

If Instr(1,campaign, obj.innerText & ";", vbTextCompare) > 0 Then             
    obj.Selected = True         
End If

All untested, but it should give you the general idea.

 

[ianraff]

That looks like the perfect solution, but I this goes back to my main problem... When I go through and debug this, the campaign variable never actually holds a value. It just says campaign = "", in here:

Dim obj As Object

    For Each obj In IE.document.all.Item("cph_filterRepeater_bvc_0").Options
        If InStr(1, campaign, obj.innerText & ";", vbTextCompare) > 0 Then
            obj.Selected = True
        End If
        
    Next obj

I have a login userform that I have this code for:

Dim IE As SHDocVw.InternetExplorer
    Dim URL As String
    Dim userName As String
    Dim password As String
    Dim campaign As String
    Dim i As String
    URL = "https://lm.leads360.com"
'   Load userform - velocifyLogin

velocifyLogin.Show

userName = velocifyLogin.userNameText
password = velocifyLogin.passwordText

Which is used and works here:

With IE
    .document.getElementById("usernameTextBox").Value = userName
    .document.getElementById("passwordTextBox").Value = password
    .document.getElementById("loginButton").Click

Do I have to do the same thing for the campaign variable? I'm still confused about the passing of variables between userforms and my main sub. Thanks for your help!!

 

[ianraff]

Nevermind!! I finally figured out what I needed to do. It's silly, really. I wasn't thinking logically enough. If anyone is following this, here's the solution:

The userform code shouldn't contain any variables or code really (at least in this example). I referenced this: Introduction to UserForms: Part II

So my solution was removing this:

Dim campaign As String
    Dim i As Integer
' Campaigns are stored in hidden worksheet
For i = 0 To campaignselect.ListBox1.ListCount - 1
        If campaignselect.ListBox1.Selected(i) = True Then
            campaign = campaign & campaignselect.ListBox1.List(i) & ";"
        End If
    Next i

From the selectButton_Click() sub on the userform, and placing within the mainsub. The code for my userform now looks like this:

Private Sub cancelButton_Click()
    Unload campaignselect
End Sub

Private Sub selectButton_Click()
    Hide
End Sub

Private Sub campaignSelect_Initialize()
    campaignselect.ListBox1.RowSource = Range("Campaigns").Address
End Sub

And my main sub looks like this (I know it's messy right now, still need to go through - proof and edit):

Option Explicit

Sub Conversion()
'
'   Intended to automate Marketing conversion reports
'
    Dim IE As SHDocVw.InternetExplorer
    Dim URL As String
    Dim userName As String
    Dim password As String
    Dim campaign As String
    Dim i As Integer
    URL = "https://lm.leads360.com"
'   Load userform - velocifyLogin

velocifyLogin.Show

userName = velocifyLogin.userNameText
password = velocifyLogin.passwordText
    
   campaignselect.campaignSelect_Initialize
    
   campaignselect.Show
   
' Campaigns are stored in hidden worksheet
For i = 0 To campaignselect.ListBox1.ListCount - 1
        If campaignselect.ListBox1.Selected(i) = True Then
            campaign = campaign & campaignselect.ListBox1.List(i) & ";"
        End If
    Next i


'   Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate URL
    
'   Wait sequence while IE loads...
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
'
'
'   Insert Login & Password --> Submit --> Go to Custom Reports
With IE
    .document.getElementById("usernameTextBox").Value = userName
    .document.getElementById("passwordTextBox").Value = password
    .document.getElementById("loginButton").Click
    .navigate "https://lm.leads360.com/Web/ReportsCustom.aspx"
'
'   Wait sequence while IE loads...
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
'
'   Navigate to custom Conversion Report
With IE
.document.getElementById("cph_searchTextBox").Value = "Ian_Automate"
.document.getElementById("cph_searchButton").Click

'   IE takes too long to load. Pause macro

    Application.Wait (Now + TimeValue("0:00:03"))

Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

.document.getElementById("cph_reportRepeater_runLinkButton_0").Click

Application.Wait (Now + TimeValue("0:00:02"))
Dim obj As Object

    For Each obj In IE.document.all.Item("cph_filterRepeater_bvc_0").Options
        If InStr(1, campaign, obj.innerText & ";", vbTextCompare) > 0 Then
            obj.Selected = True
        End If
        
    Next obj

End With
End With
End Sub

Thanks so much for your help John_W!!!!!

 

[ianraff]

Problem: Is there an exact match version of vbTextCompare? There are some items in the list with similar names, so it grabs both/all of them containing one or more of the same string...

 

[John_w]

vbTextCompare gives a case-insensitive match. Try vbBinaryCompare or omit the 1st and 4th arguments from Instr altogether for a case-sensitive match.

The ";" separating each campaign listbox value and then looking for obj.innerText & ";" should ensure that it doesn't select a campaign option value which is a substring of another campaign option value. (Unless ";" is part of an option value, in which case use a different separator!). But it all assumes that the ListBox1 values are exactly the same as the IE.document.all.Item("cph_filterRepeater_bvc_0").Options, apart from the case of characters. If there are leading or trailing spaces in the option value you can ignore them by using Trim(obj.innerText) & ";".

 

[ianraff]

Thanks so much for your patience and sticking with me on this, man. I tried adjusting all of that... Here's an example of what it's doing, no matter what though.

In my listbox, I'm choosing "Bank Referral" and in the webpage item("cph_filterRepeater_bvc_0") it selects the "Bank Referral" option like it should, but there's another option in that item called "Referral" that it also selects. Same goes for a campaign called, "Contest", which also selects an item called "Test".... Haven't found others it's doing that for, yet. Any ideas why it's doing that?

I tried vbTextCompare with a different separator, trim, vbBinary with those as well... The variable is assigning correctly from the userform for all combinations, it just selects extra options on the webpage...