Posts by John_w


    Recent tests with the UIAutomation_Click_IE_Tab procedure in post #2 generated the error:

    Run-time error '91':
    Object variable or With block variable not set

    At the following line:

    Set IEtabPattern = IEtab.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)

    The IEtab variable is Nothing because the previous line didn't find the specified tab name.

    I discovered that sometimes, although the visible the tab name - shown when hovering over the tab - may be "xxxxx", the actual tab name according to UIAutomation is "xxxxx Tab Group 1". Therefore looking for a tab named "xxxxx" will fail.

    The solution is to call UIAutomation_Click_IE_Tab like this:

    UIAutomation_Click_IE_Tab IE.hwnd, IE.LocationName & " Tab Group 1"

    Alternatively, here is a new procedure which uses the Like operator, so wildcards can be used (see to specify the tab name to be found and activated.

    For example, call it like this:

    UIAutomation_Click_IE_Tab_Like IE.hwnd, IE.LocationName & "*"

    $15 is a little low for the time and work involved to address the issues. I would do it for $25.

    I3:I12 and T14 reference a cell in second workbook, [Budget Planner-New.xls]Budget'!$A$1, which you haven't included in the .zip. I assume A1 is a date. Do you want to keep this second workbook and have the code read the date from A1?

    I can't really help you further then, and that tiny image doesn't give more information. You didn't answer my question about events.

    Something like this might work, as long as HTMLdoc is the correct document (i.e. no frames). It also shows how to trigger the click event on the dropdown. This code uses early binding of the HTML classes, so you must set a reference to MS HTML Object Library, via Tools->References in the VBA editor.

    Use Task Scheduler to run a VBScript (.vbs) file every 30 minutes. The .vbs file opens the workbook, which automatically refreshes the CSV data (I assume you have a text import query which refreshes the data when opening the file). Then write a macro which calls Application.OnTime 30 seconds into the future which 'allows the updates to happen' (I'm not sure what you mean by this) and Saves As a CSV file and closes the workbook and the Excel application. The Save As and closing a workbook code can be generated by recording a macro and editing the generated code. There should be examples of all this on this forum.

    Which columns does the table occupy? This code assumes the table is in columns V:AA, so column Z is the 5th column (hence the Field:=5 in the code).

    Try this, which searches the select dropdown for the required visible text and, if found, sets the select element's value property.

    The code requires a reference to MS HTML Object Library - set via Tools - References in VBA.

    With IE11 you need to change the call to

    Dim Tabname
    Tabname= IE.Document.Title
    If Tabname= "" Then Tabname= IE.Document.Location.hostname
    Call UIAutomation_Click_IE_Tab(IE.hwnd, Tabname)

    In all my tests with IE11 it finds the required tab, which can be specified as the tab's URL or LocationName, including wildcards if needed. Can you give examples of web sites or pages where your change is needed?