求助：从带密码保护且无表格ID的网站导入数据

Let’s break down why your original code is failing and how to fix it—plus a more reliable modern alternative:

Common Issues with Your Original Code #

• InternetExplorer is deprecated: Microsoft has phased out IE, leading to compatibility crashes, stuck processes, and unresponsive behavior.
• Missing login handling: The original snippet didn’t include steps to enter your credentials and submit the login form, which is mandatory for accessing protected content.
• No page load waits: Trying to interact with elements before the page fully loads causes the code to get stuck or throw errors.

Corrected IE-Based VBA Code (With Login Handling) #

This version adds proper login steps, load waits, and error checking:

Sub GetProtectedTable()
    Dim ieApp As InternetExplorer
    Dim ieDoc As HTMLDocument
    Dim ieTable As HTMLTable
    Dim clip As DataObject
    Dim usernameInput As Object
    Dim passwordInput As Object
    Dim loginButton As Object
    
    ' Initialize IE (visible for debugging; set to False for silent runs)
    Set ieApp = New InternetExplorer
    ieApp.Visible = True
    
    ' Navigate to the target page
    ieApp.Navigate "https://www.vesseltracker.com/fr/Ports/Home.html"
    
    ' Wait for initial page load
    Do While ieApp.Busy Or ieApp.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    Set ieDoc = ieApp.Document
    
    ' Find login elements (use browser dev tools F12 to get actual IDs/names)
    On Error Resume Next
    Set usernameInput = ieDoc.getElementById("username") ' Replace with real selector
    Set passwordInput = ieDoc.getElementById("password") ' Replace with real selector
    Set loginButton = ieDoc.getElementById("loginBtn") ' Replace with real selector
    On Error GoTo 0
    
    ' Handle missing elements
    If usernameInput Is Nothing Or passwordInput Is Nothing Or loginButton Is Nothing Then
        MsgBox "Could not locate login fields. Check selectors with browser dev tools."
        ieApp.Quit
        Set ieApp = Nothing
        Exit Sub
    End If
    
    ' Enter credentials and submit login
    usernameInput.Value = "YOUR_USERNAME"
    passwordInput.Value = "YOUR_PASSWORD"
    loginButton.Click
    
    ' Wait for post-login page to load
    Do While ieApp.Busy Or ieApp.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' Locate the target table (adjust selector to match your table)
    Set ieTable = ieDoc.getElementsByTagName("table")(0) ' Or use table ID if available
    
    ' Copy table to clipboard and paste into Excel
    Set clip = New DataObject
    clip.SetText ieTable.outerHTML
    clip.PutInClipboard
    ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial
    
    ' Cleanup
    ieApp.Quit
    Set ieApp = Nothing
    Set ieDoc = Nothing
    Set ieTable = Nothing
    Set clip = Nothing
End Sub

Key Notes for This Code #

• Inspect the website: Use your browser’s developer tools F12 to find the actual IDs/names of login fields and the login button—replace the placeholders in the code with these values.
• Wait loops: The Do While ieApp.Busy blocks ensure the code doesn’t interact with elements until the page is fully loaded, preventing stuck processes.
• Error handling: The On Error blocks catch cases where elements aren’t found, giving you a clear message instead of a crash.

Better Modern Alternative: Selenium with VBA #

Since IE is deprecated, Selenium (works with Chrome/Edge) is far more reliable. Here’s a quick example:

First, install the Selenium VBA wrapper via the VBA editor’s references menu.

Sub GetTableWithSelenium()
    Dim driver As New ChromeDriver
    Dim table As WebElement
    
    ' Initialize Chrome driver
    driver.Start "chrome"
    driver.Get "https://www.vesseltracker.com/fr/Ports/Home.html"
    
    ' Enter credentials and login
    driver.FindElementById("username").SendKeys "YOUR_USERNAME"
    driver.FindElementById("password").SendKeys "YOUR_PASSWORD"
    driver.FindElementById("loginBtn").Click
    
    ' Wait for table to load (adjust wait time as needed)
    driver.Wait 5000
    
    ' Locate and copy table data to Excel
    Set table = driver.FindElementByTagName("table")
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = table.Text
    
    ' Cleanup
    driver.Quit
End Sub

Troubleshooting Tips #

• Stuck code: Check for pop-ups like cookie consent that block interaction—add code to accept cookies first if needed.
• Element not found: Double-check selectors with dev tools; some sites use dynamic IDs, so use XPath or CSS selectors instead.
• Anti-scraping blocks: Add delays (Wait commands) or mimic human behavior like scrolling to avoid being blocked.

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