求助：用VBA在medicines.ie定位表单并填入Excel单元格值

Let's get your VBA script working again now that the website's form IDs have changed. Here's a practical, updated solution:

1. What Went Wrong with Your Original Code #

Your original script relied on getElementById("input") to target the search box, but the website update removed that specific ID. We need to switch to more stable element selectors that won't break as easily with site changes.

2. Revised VBA Code #

This script works with the current version of medicines.ie and handles potential loading delays:

Sub Medicinesie()
    Dim IE As Object
    Dim searchBox As Object
    Dim searchButton As Object
    Const READYSTATE_COMPLETE = 4 ' Define the ready state constant to avoid errors
    
    ' Initialize Internet Explorer instance
    Set IE = CreateObject("INTERNETEXPLORER.APPLICATION")
    IE.navigate "http://www.medicines.ie/"
    IE.Visible = True
    
    ' Wait for the main page to fully load (double-check both IE and document states)
    Do
        DoEvents
    Loop Until IE.ReadyState = READYSTATE_COMPLETE And IE.Document.ReadyState = "complete"
    
    ' Wait for the search box to render (handles slow-loading elements)
    Do
        On Error Resume Next
        Set searchBox = IE.Document.querySelector("input[name='search-term']")
        On Error GoTo 0
        DoEvents
    Loop Until Not searchBox Is Nothing
    
    ' Populate the search box with your Excel cell value
    searchBox.Value = Range("spc").Value
    
    ' Wait for the search button to become available
    Do
        On Error Resume Next
        Set searchButton = IE.Document.querySelector("button.search-submit")
        On Error GoTo 0
        DoEvents
    Loop Until Not searchButton Is Nothing
    
    ' Click the search button (mimics user behavior better than form submission)
    searchButton.Click
    
    ' Wait for the results page to finish loading
    Do
        DoEvents
    Loop Until IE.ReadyState = READYSTATE_COMPLETE And IE.Document.ReadyState = "complete"
    
    ' Optional cleanup: uncomment when done debugging
    ' Set IE = Nothing
End Sub

3. Key Improvements Explained #

• Stable element targeting: Uses querySelector with CSS selectors instead of fragile IDs. The search box is targeted by its name attribute (search-term), and the button by its class (search-submit)—these are less likely to change with site updates.
• Loading safeguards: Adds loops to wait for elements to render, preventing "element not found" errors from slow page loads.
• User-like interaction: Clicks the actual search button instead of submitting the form directly, ensuring any JavaScript-powered search logic runs correctly.

4. How to Update Selectors If the Site Changes Again #

If medicines.ie updates later, you can find new selectors in 3 quick steps:

1. Open the site in Chrome/Firefox and press F12 to open Developer Tools.
2. Use the "Select Element" tool (cursor icon) to click the search box or button.
3. In the HTML panel, grab attributes like name, class, or type to use in querySelector (e.g., input[placeholder='Search for medicines...']).

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