将VBA代码转换为SSIS脚本任务可用的VB.NET：IE自动化下载Excel遇阻

Hey there! Let's work through this IE Automation + UI Automation snag you're hitting when porting your VBA solution to VB.NET for SSIS Script Tasks. I've helped folks troubleshoot similar cross-language automation issues before, so let's break this down step by step.

First, Let's Cover the Core VB.NET Code (With SSIS-Specific Tweaks) #

First, make sure you've added references to:

• UIAutomationClient and UIAutomationTypes (you mentioned this already)
• Microsoft Internet Controls (SHDocVw)
• Microsoft HTML Object Library (MSHTML)

Here's the adapted code, with fixes for common VB.NET/SSIS pitfalls:

Imports System.Windows.Automation
Imports SHDocVw
Imports MSHTML
Imports System.Threading

Public Class ScriptMain
    Public Sub Main()
        ' Critical: UI Automation requires STA threading (SSIS defaults to MTA)
        Dim staWorker As New Thread(AddressOf ExecuteDownloadWorkflow)
        staWorker.SetApartmentState(ApartmentState.STA)
        staWorker.Start()
        staWorker.Join()

        Dts.TaskResult = ScriptResults.Success
    End Sub

    Private Sub ExecuteDownloadWorkflow()
        Try
            ' 1. Locate your target IE window (adjust the title match as needed)
            Dim targetIE As InternetExplorer = GetIEByWindowTitle("Your Target Page Title")
            If targetIE Is Nothing Then
                Throw New Exception("Could not find the target IE window")
            End If

            ' 2. Wait for IE to finish loading the page
            WaitForIELoad(targetIE)

            ' 3. Grab IE's window handle and hook into UI Automation
            Dim ieWindowHandle As IntPtr = New IntPtr(targetIE.HWND)
            Dim ieAutomationRoot As AutomationElement = AutomationElement.FromHandle(ieWindowHandle)

            ' 4. Locate the Frame Notification Bar (download prompt)
            Dim notificationBarCondition As Condition = New AndCondition(
                New PropertyCondition(AutomationElement.ControlTypeProperty, ControlType.ToolBar),
                New PropertyCondition(AutomationElement.NameProperty, "Frame Notification Bar") ' Adjust for localized IE (e.g., "框架通知栏" for Chinese)
            )
            Dim downloadBar As AutomationElement = WaitForElement(ieAutomationRoot, notificationBarCondition, TimeSpan.FromSeconds(15))
            
            If downloadBar Is Nothing Then
                Throw New Exception("Download notification bar never appeared")
            End If

            ' 5. Find and click the "Save" button (adjust name for your IE language/version)
            Dim saveButtonCondition As Condition = New AndCondition(
                New PropertyCondition(AutomationElement.ControlTypeProperty, ControlType.Button),
                New PropertyCondition(AutomationElement.NameProperty, "Save")
            )
            Dim saveButton As AutomationElement = downloadBar.FindFirst(TreeScope.Children, saveButtonCondition)
            
            If saveButton Is Nothing Then
                Throw New Exception("Save button not found on the notification bar")
            End If

            ' Trigger the button click via UI Automation
            Dim invokePattern As InvokePattern = DirectCast(saveButton.GetCurrentPattern(InvokePattern.Pattern), InvokePattern)
            invokePattern.Invoke()

            ' Optional: Add logic here to handle the "Save As" dialog if needed
        Catch ex As Exception
            Dts.Events.FireError(0, "Download Failed", ex.Message, String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub

    ' Helper: Find IE instance by window title
    Private Function GetIEByWindowTitle(titleContains As String) As InternetExplorer
        Dim shellWindows As New ShellWindows()
        For Each ieInstance As InternetExplorer In shellWindows
            If ieInstance.LocationName.IndexOf(titleContains, StringComparison.OrdinalIgnoreCase) >= 0 Then
                Return ieInstance
            End If
        Next
        Return Nothing
    End Function

    ' Helper: Wait for IE to finish loading
    Private Sub WaitForIELoad(ie As InternetExplorer)
        Do While ie.Busy Or ie.ReadyState <> tagREADYSTATE.READYSTATE_COMPLETE
            Thread.Sleep(200)
        Loop
    End Sub

    ' Helper: Wait for a UI Automation element to appear (avoids race conditions)
    Private Function WaitForElement(parent As AutomationElement, condition As Condition, timeout As TimeSpan) As AutomationElement
        Dim startTime As DateTime = DateTime.Now
        Do
            Dim foundElement As AutomationElement = parent.FindFirst(TreeScope.Descendants, condition)
            If foundElement IsNot Nothing Then Return foundElement
            Thread.Sleep(300)
        Loop While DateTime.Now.Subtract(startTime) < timeout
        Return Nothing
    End Sub

    Enum ScriptResults
        Success = 0
        Failure = 1
    End Enum
End Class

Why Your VBA Code Worked but VB.NET Didn't #

Let's break down the key differences that often trip people up:

• Threading Model: VBA runs in a Single-Threaded Apartment (STA) by default, which UI Automation requires. SSIS Script Tasks default to Multi-Threaded Apartment (MTA), which breaks UI Automation. The code above explicitly spins up an STA thread to handle the workflow—this is non-negotiable.
• Element Timing: VBA's DoEvents can sometimes mask race conditions where the download bar isn't fully loaded yet. The VB.NET code uses explicit waits for elements to appear, which is more reliable for SSIS's runtime environment.
• 64-bit/32-bit Mismatch: If your SSIS package runs in 64-bit mode but you're using 32-bit IE, the SHDocVw/MSHTML references won't work correctly. Go to your SSIS project properties and set Run64BitRuntime to False to match IE's architecture.
• Localized Element Names: The Frame Notification Bar's name changes based on IE's language settings. Use the Windows SDK's Inspect.exe tool to verify the exact name property for your IE instance.

Debugging Tips for SSIS #

1. Add Logging: Use Dts.Events.FireInformation to log each step (e.g., "Found IE window", "Waiting for download bar")—this helps you pinpoint where the workflow fails.
2. Test Outside SSIS First: Run the core download logic in a standalone VB.NET console app first. If it works there, the issue is likely SSIS-specific (threading, permissions, architecture).
3. Check IE Security Settings: Ensure IE allows automation—go to Internet Options > Security > Custom Level, and enable "Allow script-initiated windows without size or position constraints".

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