PDA

View Full Version : [SOLVED] VBA code to search for online data and import into excel



vitovabo
06-20-2016, 06:28 AM
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
06-20-2016, 06:31 AM
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
06-20-2016, 07:01 AM
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
06-20-2016, 07:32 AM
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
06-20-2016, 07:57 AM
We don't agree.

Call HRM.

vitovabo
06-20-2016, 08:00 AM
thats pretty lame..

Kenneth Hobs
06-20-2016, 08:12 AM
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
06-20-2016, 08:27 AM
Never mind i figured it out. I'm Pretty much a genius at this point.

snb
06-20-2016, 09:27 AM
We are always short of geniuses, so feel free to share your solution ( that's what a forum is all about).