Convert VBA Code to VB.NET for use in SSIS Script Task

Question

The purpose of this is to download an excel file from internet explorer using IE Automation and UI Automation to work with the Frame Notification Bar in internet explorer.

After spending quite some time with UI Automation in VBA, I have successfully gotten this to work.

I now need to write this in VB to use in a script task as part of a larger SSIS package. I am running into issues getting this to work after trying to modify it. I added the UIAutomation references in both Excel and SSIS script task.

Here is my working Excel VBA Code:

Option Explicit


Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr

Public xl As Excel.Application
Public ie As InternetExplorerMedium
Public iePage As HTMLDocument
Public msbExport As HTMLLinkElement

Public CUIAuto As IUIAutomation
Public WindowHandleElement As IUIAutomationElement
Public windowHandle As Long
Public tCnd As IUIAutomationCondition
Public bCnd As IUIAutomationCondition
Public tCtl As IUIAutomationElement
Public bCtl As IUIAutomationElement
Public dlStatus As String
Dim fName As String
Public InvokePattern As IUIAutomationInvokePattern


Public Sub getReport()
Set ie = New InternetExplorerMedium
ie.Visible = True
ie.navigate ("https://www.fincen.gov/fcn/financial_institutions/msb/msbstateselector.html#")

 Do: Application.Wait Now + #12:00:01 AM#: Loop Until ie.readyState = tagREADYSTATE.READYSTATE_COMPLETE And ie.Busy = False
 Set iePage = ie.document

iePage.getElementById("ExportExcelLink").Click

    Do
    'On Error Resume Next
    windowHandle = ie.Hwnd
    windowHandle = FindWindowEx(windowHandle, 0, "Frame Notification Bar", vbNullString)
    Application.Wait Now + #12:00:01 AM#
    Loop Until windowHandle > 0



Set CUIAuto = New CUIAutomation
Set WindowHandleElement = CUIAuto.ElementFromHandle(ByVal windowHandle)

Set tCnd = CUIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TextControlTypeId)
Set tCtl = WindowHandleElement.FindFirst(TreeScope_Subtree, tCnd)
dlStatus = tCtl.GetCurrentPropertyValue(UIA_ValueValuePropertyId)
fName = Mid(dlStatus, InStr(dlStatus, "1"), 14)
Debug.Print dlStatus ' leave this to see download progress in immediate window


Set bCnd = CUIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Save")
Set bCtl = WindowHandleElement.FindFirst(TreeScope_Subtree, bCnd)
Set InvokePattern = bCtl.GetCurrentPattern(UIA_InvokePatternId)
InvokePattern.Invoke

Do
dlStatus = tCtl.GetCurrentPropertyValue(UIA_ValueValuePropertyId)
Debug.Print dlStatus
DoEvents
Application.Wait Now + #12:00:01 AM#
Loop Until Right(Trim(dlStatus), 10) = "completed."
Stop


End Sub

Here is the UIAutomation part of my VB code that is not working:

#Region "Imports"
Imports System.Threading.Thread
Imports UIAutomationClient
Imports UIAutomationClient.UIA_PropertyIds
Imports UIAutomationClient.UIA_PatternIds
#End Region




Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As IntPtr

Public CUIAuto As IUIAutomation
Public WindowHandleElement As IUIAutomationElement
Public windowHandle As Long
Public tCnd As IUIAutomationCondition
Public bCnd As IUIAutomationCondition
Public tCtl As IUIAutomationElement
Public bCtl As IUIAutomationElement
Public dlStatus As String
Public InvokePattern As IUIAutomationInvokePattern
Do

windowHandle = IE.HWND
windowHandle = CLng(FindWindowEx(windowHandle, 0, "Frame Notification Bar", vbNullString))
Sleep(1000)
Loop Until windowHandle > 0



CUIAuto = New CUIAutomation
WindowHandleElement = CUIAuto.ElementFromHandle(CType(windowHandle, IntPtr))

tCnd = CUIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_ControlTypeIds.UIA_TextControlTypeId)
tCtl = WindowHandleElement.FindFirst(TreeScope.TreeScope_Subtree, tCnd)
dlStatus = CType(tCtl.GetCurrentPropertyValue(UIA_ValueValuePropertyId), String) 'get download status

bCnd = CUIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Save")
bCtl = WindowHandleElement.FindFirst(TreeScope.TreeScope_Subtree, bCnd) ' The error seems to be happening here, there is no object after running this line
InvokePattern = CType(bCtl.GetCurrentPattern(UIA_InvokePatternId), IUIAutomationInvokePattern)
InvokePattern.Invoke()


End Sub

The part that seems to not work in the VB Script task is this line

bCtl = WindowHandleElement.FindFirst(TreeScope.TreeScope_Subtree, bCnd)

score 0 · Answer 1 · answered Apr 15 '18 at 23:01

I was able to solve this. Here is the UI Automation part of my VB.NET Code. This method is far better than using sendkeys as it works with the actual object. I use the text in the frame notification bar to determine when the file has finished downloading. Just add a "Do Loop until the text reads completed. You will want to read the text on every loop so it can track the download progress.

#Region "Imports"
Imports System.Threading.Thread
Imports UIAutomationClient
Imports UIAutomationClient.UIA_PropertyIds
Imports UIAutomationClient.UIA_PatternIds
'Imports UIAutomationClient.IUIAutomationElement
#End Region

    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As IntPtr, ByVal hWnd2 As IntPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As IntPtr

    Public CUIAuto As IUIAutomation
    Public WindowHandleElement As IUIAutomationElement
    Public windowHandle As Long
    Public tCnd As IUIAutomationCondition
    Public bCnd As IUIAutomationCondition
    Public tCtl As IUIAutomationElement
    Public bCtl As IUIAutomationElement
    Public dlStatus As String
    Public InvokePattern As IUIAutomationInvokePattern


        Do
            'On Error Resume Next
            windowHandle = IE.HWND
            windowHandle = CLng(FindWindowEx(windowHandle, 0, "Frame Notification Bar", vbNullString))
            Sleep(1000)
        Loop Until windowHandle > 0



        CUIAuto = New CUIAutomation
        WindowHandleElement = CUIAuto.ElementFromHandle(CType(windowHandle, IntPtr))

        tCnd = CUIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_ControlTypeIds.UIA_TextControlTypeId)
        tCtl = WindowHandleElement.FindFirst(TreeScope.TreeScope_Subtree, tCnd)
        dlStatus = CType(tCtl.GetCurrentPropertyValue(UIA_ValueValuePropertyId), String)

        bCnd = CUIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Save")
        bCtl = WindowHandleElement.FindFirst(TreeScope.TreeScope_Subtree, bCnd)
        InvokePattern = CType(bCtl.GetCurrentPattern(UIA_InvokePatternId), IUIAutomationInvokePattern)
        InvokePattern.Invoke()

Convert VBA Code to VB.NET for use in SSIS Script Task

1 Answers1