Excel VBA如何在同一窗口/标签页中导航？新手技术求助

Hey there! Let's sort out this IE instance problem for you. The core issue with your current code is that you're creating a brand new InternetExplorer.Application object inside the loop—that's exactly why a new window spawns every iteration. Here's how to adjust it to reuse the same window/tab for all your navigation:

Modified Code #

Sub Calculate()
    Dim i As Integer
    ' Declare and initialize the IE object OUTSIDE the loop to reuse it
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False ' Keep hidden as you originally had
    
    ' Add error handling to ensure IE gets cleaned up if something goes wrong
    On Error GoTo Cleanup

    For i = 1 To 1000
        ' Navigate to the target URL in the existing IE window
        IE.navigate "https://www.somewebsite.com/" & Cells(i, 2).Value
        
        ' Wait for page to load (with a timeout to avoid infinite loops)
        Dim startTime As Date
        startTime = Now
        Do
            DoEvents
            ' Time out after 10 seconds if page doesn't load
            If DateDiff("s", startTime, Now) > 10 Then
                MsgBox "Page load timed out for URL: " & "https://www.somewebsite.com/" & Cells(i, 2).Value
                GoTo NextIteration
            End If
        Loop Until IE.readyState = 4 ' READYSTATE_COMPLETE = 4 (use the constant if defined in your module)
        
        ' Your existing data extraction logic goes here
        ' some code here . . . .

NextIteration:
    Next i

Cleanup:
    ' Ensure IE is closed and object is released even if an error occurs
    If Not IE Is Nothing Then
        IE.Quit
        Set IE = Nothing
    End If
    ' Show error message if something went wrong
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description
    End If
End Sub

Key Changes Explained #

• Move IE Initialization Outside the Loop: By declaring and creating the IE object before the loop starts, we reuse the same instance for every navigation instead of spawning a new one each time.
• Add Timeout to Wait Loop: The original Do...Loop could hang forever if a page fails to load. The 10-second timeout ensures your script keeps running even if a URL has issues.
• Error Handling & Cleanup: The Cleanup section guarantees IE is closed properly, preventing leftover background IE processes that can slow down your system.
• Use Numeric Ready State: If your module doesn't have the READYSTATE_COMPLETE constant defined, using its numeric equivalent 4 works reliably. You can also add Const READYSTATE_COMPLETE = 4 at the top of your module for readability.

Extra Tips #

• If you want to see what's happening during testing, set IE.Visible = True.
• To avoid triggering anti-scraping measures on the target site, add a small delay between iterations: Application.Wait Now + TimeValue("00:00:02") (adjust the time as needed).
• If the site opens new tabs automatically, you can add logic to switch back to the main IE window, but most well-behaved sites will navigate within the same tab when using IE.navigate.

内容的提问来源于stack exchange，提问作者O.k