基于VBA实现Excel批量自动提交Lot编号至网页查询报告的需求

Here's a practical VBA solution that will automatically open your target webpage, input each Lot number from cells B3:B7, and trigger the "view report" button. This uses Internet Explorer (compatible with most Excel versions) with proper waiting logic to ensure the page loads correctly before interacting with elements.

Step 1: Enable Required Reference #

First, make sure you’ve enabled the Microsoft Internet Controls reference in your VBA editor:

• Press Alt + F11 to open the VBA Editor.
• Go to Tools > References and check the box next to Microsoft Internet Controls.
• Click OK to save the change.

Step 2: The VBA Code #

Sub AutoSubmitLotReports()
    Dim ie As InternetExplorer
    Dim ws As Worksheet
    Dim lotCell As Range
    Dim inputBox As Object
    Dim reportButton As Object
    Dim targetURL As String
    
    ' Replace with your actual webpage URL
    targetURL = "http://blablabla"
    
    ' Initialize Internet Explorer (set Visible to False for background execution)
    Set ie = New InternetExplorer
    ie.Visible = True
    
    ' Set the worksheet containing your Lot numbers (update to your sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Loop through each Lot number in B3:B7
    For Each lotCell In ws.Range("B3:B7")
        ' Skip empty or non-numeric cells
        If Trim(lotCell.Value) <> "" And IsNumeric(lotCell.Value) Then
            ' Navigate to the target page
            ie.Navigate targetURL
            
            ' Wait for the page to fully load
            Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
                DoEvents
            Loop
            
            ' Locate the "lot name" input box (adjust selector based on your page's HTML)
            On Error Resume Next
            ' Try finding by placeholder text first (if input has placeholder="lot name")
            Set inputBox = ie.Document.querySelector("input[placeholder='lot name']")
            ' If not found, try by name attribute (e.g., name="lotname")
            If inputBox Is Nothing Then
                Set inputBox = ie.Document.getElementsByName("lotname")(0)
            End If
            ' If still not found, try by ID (e.g., id="lotname")
            If inputBox Is Nothing Then
                Set inputBox = ie.Document.getElementById("lotname")
            End If
            On Error GoTo 0
            
            If Not inputBox Is Nothing Then
                ' Input the Lot number
                inputBox.Value = lotCell.Value
                
                ' Locate the "view report" button
                On Error Resume Next
                ' Try finding by button text (adjust if your button uses different wording)
                Set reportButton = ie.Document.querySelector("button:contains('view report')")
                ' Fallback to name or ID if needed
                If reportButton Is Nothing Then
                    Set reportButton = ie.Document.getElementsByName("viewreport")(0)
                End If
                If reportButton Is Nothing Then
                    Set reportButton = ie.Document.getElementById("viewreport")
                End If
                On Error GoTo 0
                
                If Not reportButton Is Nothing Then
                    ' Click the button to load the report
                    reportButton.Click
                    
                    ' Wait for the report page to load (adjust time if needed)
                    Application.Wait Now + TimeValue("00:00:03")
                    ' For dynamic pages, replace the wait with:
                    ' Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
                    '     DoEvents
                    ' Loop
                Else
                    MsgBox "Couldn't find 'view report' button for Lot: " & lotCell.Value, vbExclamation
                End If
            Else
                MsgBox "Couldn't find 'lot name' input box for Lot: " & lotCell.Value, vbExclamation
            End If
        End If
    Next lotCell
    
    ' Clean up resources
    ie.Quit
    Set ie = Nothing
    Set ws = Nothing
    MsgBox "All Lot numbers processed successfully!", vbInformation
End Sub

Important Tips: #

• Adjust Selectors: To get the right element identifiers (ID/name/placeholder), right-click the input box or button on your webpage and select Inspect to view the HTML. Update the code’s selectors to match what you see.
• Background Execution: Set ie.Visible = False if you want the automation to run without showing the browser window.
• Wait Logic: The Application.Wait is a simple delay, but using the ie.Busy check is more reliable for pages with dynamic content.
• Error Handling: The code skips empty cells and alerts you if elements can’t be found, so you can troubleshoot specific issues easily.

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