VBA code to search for online data and import into excel

[vitovabo]

Hi I'm new to VBA, mostly used MatLab and some Python (very limited),

So at my job I was given a task of finding missing information from a company website. I need to find information such as where the employee works (building name), their first name, last name, and region of location. The only information i was provided by was their email.

What im trying to do:
I have 853 emails in excel in Column A.
I want to use VBA to open IE to the company website, enter the email from A2 and search.
Then once the information is found, to take the certain fields (from the website) and populate it back into excel Columns B,C,D,etc. to fill in their names, locations etc.

And i need this to loop until all the information for all the employees is entered.

currently my code only gets to IE, sometimes... [once i figure out how to post the code on here ill post below]

Is this even possible for VBA?



The company uses IE8 on windows 7. I upgraded to IE11 and now I'm having a "certificate verification" issue.

[vitovabo]

My next approach is to use get.elementbyID "mail" and have it range thorough the Column A ... which im not sure how to do

Sub logonjawn()

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


IE.Navigate "super secure company website goes here, only accessible with local connection through ethernet"



Do
If IE.ReadyState = 4 Then
IE.Visible = False
Exit Do
Else
DoEvents
End If
Loop
'need this timer to allow IE to load idk why but it only works like this
Application.Wait (Now + TimeValue("0:00:02"))
IE.Visible = True

[snb]

If the information is in the company's website, the webmanager has all the information you need.

And who provided the webmanager this information ?

[vitovabo]

The web manager is on vacation, his email box is full, his voicemail line is full.

And having a scraping code is much better then calling people around then fuzzylooking for similarities.

[snb]

We don't agree.

Call HRM.

[vitovabo]

thats pretty lame..

[Kenneth Hobs]

It is hard to help without a site to test or even the source code. Some source code redirects to secure java so it can not be found. If they went to the trouble to require a userid and password, I doubt that they left source code open. If they did, this might get you started.

Sub Test_LoginYahoo()    LoginYahoo ThisWorkbook.Worksheets("Main").Range("A1").Value2, _
      ThisWorkbook.Worksheets("Main").Range("A2").Value2
End Sub


'Add Tools > References...
'  Microsoft HTML Object Library
'  Microsoft Internet Controls
Sub LoginYahoo(username As String, password As String)
    Const strURL_c As String = "http://mail.yahoo.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    
    Excel.Application.Cursor = xlWait
    If InStr(username, "@") = 0 Then username = username & "@yahoo.com"
    
    On Error GoTo Err_Hnd
    
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    objIE.Visible = False
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
    '    DoEvents
    'Loop
    'Set document object
    Set ieDoc = objIE.Document
    ieDoc.getElementsByName("passwd").Item(0).Value = password
    ieDoc.getElementsByName("username").Item(0).Value = username
    ieDoc.forms("Login_form").Submit
    
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
    On Error GoTo 0
    Excel.Application.Cursor = xlDefault
End Sub

[vitovabo]

Never mind i figured it out. I'm Pretty much a genius at this point.

[snb]

We are always short of geniuses, so feel free to share your solution ( that's what a forum is all about).