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!

 

[John_w]

Think I've spotted the bug. To fix:

campaign = ";"
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

and:

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

 

[ianraff]

JOHN, DUDE!!! YOU'RE A GENIUS!!! ray:That's the solution, sure enough!

So just for my sanity's sake... this is what we're putting in the InStr(), correct?

InStr(1,;Bank Referral;,;Bank Referral;,vbTextCompare) > 0?

It compares the two strings, but wasn't working before because the beginning of the string had no separator, so when it ran out of separators it was skipping to the next match? That's some tricky stuff man!

Thanks a trillion for your help. I owe you a beer, or 5! I'm just getting into vba, any recommendations for learning other than combing these boards?

 

[John_w]

You're correct with that Instr line.

Before, InStr(1,"Bank Referral;", "Bank Referral;",vbTextCompare) > 0, and InStr(1,"Bank Referral;", "Referral;",vbTextCompare) > 0 were both True, the second because "Referral;" is found within "Bank Referral;". Now, InStr(1,";Bank Referral;", ";Referral;",vbTextCompare) > 0 is not True because ";Referral;" is not found within ";Bank Referral;". It just needed a leading and trailing separator ";" between each substring.

To learn VBA, search the Excel message boards; use the Macro Recorder and edit the code; use the VBA debugger (F8 key etc.); add Debug.Print statements and the Immediate Window and Locals window. Also, always use Option Explicit at the top of every module (there is an editor setting to add this automatically) and declare variables with the appropriate data type.