投稿‎ > ‎

VBAで遊ぼう④

posted Mar 18, 2016, 1:56 AM by Zhang Wenxu   [ updated Apr 13, 2016, 5:34 PM ]
Public Const C_DEVNET_ID = "devnet_id"
Public Const C_DEVNET_SQL = "devnet_seq"

Public Sub updateDevNetNode()
    Dim fromNID As String
    Dim path As String    
    Dim cel As Range
    Dim ie As InternetExplorer
    Dim searchBox As Object
    Dim searchBtn As Object
    Dim id As String
    Dim row As Range
    Dim selCnt As Integer

    fromNID = getIDFromString(Cells(1, "A").value)
    Set ie = getProcenter()
    Set searchBox = getInputByName(ie, "@i_ID=")
    searchBox.value = fromNID
    Set searchBtn = getInputByValue(ie, "検索")
    Call searchBtn.Click
    Call waitIE(ie)
    path = ActiveCell.Value
    Set cel = ActiveCell
    Set row = Rows(cel.row)
    selCnt = row.Cells.Count
    For Each cel In row.Cells
        DoEvents
        id = getID(cel)
        If id <> "" Then
            Exit For
        End If
    Next
    Call listDevNetContinueFirst(id, path, id)
    Call SetWindowPos(ie.hWnd, HWND_BOTTOM, 0, 0, 0, 0, SWP_SHOWWINDOW + SWP_NOMOVE + SWP_NOSIZE)
 End Sub

Public Function findLinkByHref(href As String) As Object 
    Dim link As Object
    Dim ie As InternetExplorer
    
    Set ie = getProcenter()
    Set getLinkByHref = Nothing
    For Each link In ie.document.frames("sidetree").document.links
        DoEvents
        If link.href = href Then
            Set findLinkByHref = link
            Exit Function
        End If
    Next
End Function

Public Function getIDFromString(str As String) As String
    Dim re As New RegExp
    Dim matches As Variant
    getIDFromString = ""
    On Error GoTo EXIT_SUB
    re.Pattern = "\d{10,10}" 
    Set matches = re.Execute(str)
    getIDFromString = matches.Item(0).value
EXIT_SUB:
End Function
 
Public Function getInputByAlt(ie As InternetExplorer, alt As String) As Object
    Dim frm As Variant
    Dim i As Integer
    Dim inp As Variant
    Set getInputByAlt = Nothing
    If ie.document.frames.length > 0 Then
        For i = 0 To ie.document.frames.Length - 1
            Set frm = ie.document.frames(i)
            For Each inp In frm.document.getElementsByTagName("INPUT")
                If inp.alt = alt Then
                    Set getInputByAlt = inp
                    Exit Function
                End If
           Next
       Next
     End If
        For Each inp In ie.document.getElementsByTagName("INPUT")
            If inp.alt = alt Then
                Set getInputByAlt = inp
                Exit Function
            End If
       Next
End Function

Comments