EhshYGqy

1Sub ImportCreditorInfoFromDPP()
2    'check client_banking_info exists or create it
3    createClientNoteSheet
4    'to refer to the running copy of Internet Explorer
5    Dim ie As InternetExplorer
6    'New IE instance
7    Set ie = New InternetExplorer
8    'to refer to the HTML document returned
9    Dim html As HTMLDocument
10    'open Internet Explorer in memory, and go to website
11    Dim clientId As String
12    'This is where we are going to get the client ID's from
13    Dim clientIdListSheet As String
14    'Grab info for list
15    clientIdListSheet = ActiveWorkbook.Sheets("Master").Range("B3").Value
16    'Our DPP prefix
17    Dim dppUrl As String
18    'Ser our endpoint
19    dppUrl = ActiveWorkbook.Sheets("Master").Range("B4").Value
20    'Dont Show IE working
21    ie.Visible = False
22    'Set vars for our loop
23    Dim i As Integer
24    Dim clientCount As Integer
25    clientCount = Worksheets(clientIdListSheet).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
26    'Loop through all Id's to get the info
27    For i = 2 To clientCount
28        'Set this client ID
29        clientId = Worksheets(clientIdListSheet).Range("A" & i).Value
30        'Replace with cell of ID number
31        ie.navigate dppUrl & "?module=contacts&page=view2&cid=" & clientId
32        'Wait until IE is done loading page
33        Do While ie.READYSTATE <> READYSTATE_COMPLETE
34            Application.StatusBar = "(" & i - 1 & " out of " & clientCount - 1 & ") Pulling data for client " & clientId
35            DoEvents
36        Loop
37        'Check to see if we are authenticated if not then Lets do some Auth!!
38        If ie.document.Title = "Consumers Legal Aid" Then
39            'Ask for username and password
40            Dim username As Variant
41            Dim password As Variant
42            'Set username and password vars
43            username = InputBox("Please Enter Your DPP Userame", "Username")
44            password = InputBox("Please Enter Your DPP Password", "Password")
45            'Enter Username and PW into login
46            ie.document.getElementById("Username").Value = username
47            ie.document.getElementById("Password").Value = password
48            ie.document.forms(0).submit
49            'Wait for login to be complete again
50            Do While ie.READYSTATE <> 4 Or ie.Busy = True
51                DoEvents
52            Loop
53        End If
54        
55        'Set our HTML vars
56        Dim noteslist As HTMLDivElement
57        Dim note As HTMLObjectElement
58        
59        Dim currentNote As Integer
60    
61        'set our html document variables
62        Dim note_type As String
63        Dim created_at As String
64        Dim created_by As String
65        Dim noteContent As String
66        'show text of HTML document returned
67        Set html = ie.document
68        'We are gonna get our table of creditors
69        Set noteslist = html.getElementById("notesA")
70        
71        'Loop through the table and grab dem values!!
72        For Each note In noteslist.getElementsByClassName("left ml20 w20")
73            note_type = note.getElementsByTagName("strong")(0).innerText
74            created_by = note.getElementsByClassName("blue")(0).innerText
75            created_at = Replace(Replace(Replace(note.innerText, note_type, ""), created_by, ""), Chr(10), "")
76            noteContent = note.NextSibling.innerText
77            
78            nextEmptyRow = Worksheets("client_notes").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count + 1
79            
80            ActiveWorkbook.Sheets("client_notes").Range("A" & nextEmptyRow).Value = clientId
81            ActiveWorkbook.Sheets("client_notes").Range("B" & nextEmptyRow).Value = note_type
82            ActiveWorkbook.Sheets("client_notes").Range("C" & nextEmptyRow).Value = created_at
83            ActiveWorkbook.Sheets("client_notes").Range("D" & nextEmptyRow).Value = created_by
84            ActiveWorkbook.Sheets("client_notes").Range("E" & nextEmptyRow).Value = noteContent
85        Next note
86    Next i
87    'close down IE and reset status bar
88    ie.Quit
89    Set ie = Nothing
90    Application.StatusBar = ""
91End Sub