VBA处理dgftebrc网站验证码及批量提取结果表格技术问询

Got it, let's fix that verbose cell-by-cell extraction and streamline your workflow. The main pain points in your original code are the hardcoded XPath calls for every cell and the fixed 10-second wait that wastes unnecessary time. Here's an optimized version that pulls entire tables dynamically and makes captcha input way more user-friendly:

Optimized Code #

Option Explicit
Public Sub IECsite_Optimized()
    Dim bot As WebDriver
    Dim iecCode As String
    Dim captchaInput As String
    Dim currentRow As Long
    Dim outputStartCol As Long
    Dim tableRows As WebElements
    Dim tableRow As WebElement
    Dim tableCells As WebElements
    Dim cellIndex As Integer
    Dim rowOffset As Integer ' Tracks multiple rows per IEC result
    
    Set bot = New WebDriver
    bot.Start "chrome"
    
    currentRow = 1
    outputStartCol = 14 ' Column N (adjust this if you want to write to a different starting column)
    
    ' Loop through each IEC code in Column A
    While Len(Range("A" & currentRow)) > 0
        iecCode = Range("A" & currentRow).Value
        bot.Get "http://dgftebrc.nic.in:8090/MiscQry/Pan_index.jsp"
        
        ' Enter IEC code into the input field
        bot.FindElementById("panNo").SendKeys iecCode
        
        ' Prompt user for captcha (no fixed wait—enter immediately when ready)
        captchaInput = InputBox("Enter captcha for IEC: " & iecCode, "CAPTCHA Required", "")
        If captchaInput = vbNullString Then
            MsgBox "Captcha input cancelled. Exiting process."
            bot.Quit
            Exit Sub
        End If
        bot.FindElementById("captVal").SendKeys captchaInput
        
        ' Submit the form
        bot.FindElementById("submit").Click
        
        ' Short wait to ensure results page loads (adjust timeout if needed)
        bot.Wait 2000
        
        ' Try to locate the results table rows
        On Error Resume Next
        Set tableRows = bot.FindElementsByXPath("//table[2]/tbody/tr")
        On Error GoTo 0
        
        If Not tableRows Is Nothing Then
            rowOffset = 0
            ' Loop through each row in the results table
            For Each tableRow In tableRows
                ' Get all cells in the current row
                Set tableCells = tableRow.FindElementsByTag("td")
                
                ' Write each cell's value to Excel
                For cellIndex = 1 To tableCells.Count
                    Cells(currentRow + rowOffset, outputStartCol + cellIndex - 1).Value = tableCells(cellIndex).Text
                Next cellIndex
                
                rowOffset = rowOffset + 1 ' Move down for next table row
            Next tableRow
            
            ' Jump to the next empty row after all results for this IEC
            currentRow = currentRow + rowOffset
        Else
            ' Handle cases where captcha was wrong or no results exist
            Range("N" & currentRow).Value = "No results / Invalid captcha"
            currentRow = currentRow + 1
        End If
    Wend
    
    bot.Quit
    MsgBox "IEC lookup finished!", vbInformation
End Sub

Key Improvements Explained #

• Dynamic Table Extraction: Instead of hardcoding XPath for every single cell, we fetch all rows of the target table and loop through each row/cell pair. This automatically handles up to 15 rows without writing extra repetitive code.
• User-Friendly Captcha Flow: Replaced the fixed 10-second wait with an input box tied directly to each IEC code. You enter the captcha as soon as it's visible, no wasted waiting time.
• Multi-Row Handling: The rowOffset variable ensures that if an IEC returns multiple rows, they're written to consecutive Excel rows instead of overwriting the same line.
• Error Resilience: Added a check for the table's existence to handle invalid captchas or empty results gracefully, so the script doesn't crash mid-process.
• Adjustable Output: The outputStartCol variable lets you easily change where the table data is written (currently set to Column N).

Quick Tips #

• If the portal changes the table structure (e.g., it's no longer the second table), update the XPath //table[2]/tbody/tr to match the correct table. Use your browser's inspect tool to find a more reliable selector (like a class name if the table has one).
• Increase the bot.Wait 2000 value to 3000 or 5000 if the results page takes longer to load.
• Ensure you have the latest Selenium VBA bindings installed to avoid compatibility issues.

内容的提问来源于stack exchange，提问作者Amit Shah