TableOfBooks/autoFilling at master · Azarchaniel/TableOfBooks · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Attribute VB_Name = "autoFilling"
Public Function fillBookByISBN(ISBN As String, currentRow As String)
    Application.StatusBar = "Volam GoodReads"
    Dim APIkey As String
    Dim APIurl As String
    Dim xmlObj As MSXML2.XMLHTTP60

    Set xmlObj = New MSXML2.XMLHTTP60

    APIkey = "cvAALPZ596Xc4Fnrv6pnw"
    APIurl = "https://www.goodreads.com/book/isbn/" & ISBN & "?key=" & APIkey

    xmlObj.Open bstrMethod:="GET", bstrURL:=APIurl
    xmlObj.Send

    If xmlObj.statusText <> "OK" Then
        fillBookByISBN = False
        Exit Function
    End If

    Dim xmlDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList
    Dim xNode As MSXML2.IXMLDOMNode

    Set xDoc = New MSXML2.DOMDocument60
    xDoc.LoadXML (xmlObj.responseText)

    Dim title As String
    Dim authorNodes As MSXML2.IXMLDOMNodeList
    Dim authorNode As MSXML2.IXMLDOMNode
    Dim author(1 To 10) As String 'array author(1) = "somebody", author(2) = ...
    Dim translator As String
    'Dim editor As String
    Dim ilustrator As String
    Dim publisher As String
    Dim language As String
    Dim yearTemp As String
    Dim year As Integer

    Set xNodes = xDoc.SelectNodes("//GoodreadsResponse/book/authors/author")
    Dim temp As String

    For Each xNode In xNodes
        Select Case xNode.SelectSingleNode("role").Text
            Case ""
                Dim IterAuthor: IterAuthor = 1
                If (Len(author(IterAuthor)) > 1) Then
                    IterAuthor = IterAuthor + 1
                    author(IterAuthor) = switchNames(xNode.SelectSingleNode("name").Text)
                Else
                    author(IterAuthor) = switchNames(xNode.SelectSingleNode("name").Text)
                End If
            Case "Translator"
                If Len(translator) > 1 Then
                    translator = translator & "; " & switchNames(xNode.SelectSingleNode("name").Text)
                Else
                    translator = switchNames(xNode.SelectSingleNode("name").Text)
                End If
            Case "Ilustrator"
                If Len(ilustrator) < 1 Then
                    ilustrator = ilustrator & "; " & switchNames(xNode.SelectSingleNode("name").Text)
                Else
                    ilustrator = switchNames(xNode.SelectSingleNode("name").Text)
                End If
        End Select
    Next

    title = xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("title").Text
    publisher = xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("publisher").Text
    yearTemp = xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("publication_year").Text
    If Len(yearTemp) > 0 And IsNumeric(yearTemp) Then
        year = CInt(yearTemp)
    End If

    Select Case xDoc.ChildNodes.Item(1).ChildNodes.Item(1).SelectSingleNode("language_code").Text
        Case "slo"
            language = "sk"
        Case "cze"
            language = "cz"
        Case "eng"
            language = "en"
        Case Else
            language = ""
    End Select

    Call TurnOffCalc

    For i = 1 To 10
        Cells(currentRow, i) = author(i)
    Next i
    Cells(currentRow, 12) = ilustrator
    Cells(currentRow, 13) = translator
    Cells(currentRow, 14) = title
    Cells(currentRow, 21) = publisher
    Cells(currentRow, 22) = year
    Cells(currentRow, 23) = language
    Cells(currentRow, 32) = Format(Date, "d.m.yyyy")

    Call TurnOnCalc
    Application.StatusBar = False
    fillBookByISBN = True
End Function