Posts by John_w

If the browser is IE, looping through Shell.Windows will find all IE windows and tabs (and also File Explorer windows), from which you can extract the LocationURL property.

UPDATE

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 https://docs.microsoft.com/en-us/off.../like-operator) to specify the tab name to be found and activated.

#If VBA7 Then
Private Sub UIAutomation_Click_IE_Tab_Like(IEhwnd As LongPtr, findTabName As String)
#Else
Private Sub UIAutomation_Click_IE_Tab_Like(IEhwnd As Long, findTabName As String)
#End If
  
    Dim UIauto As IUIAutomation
    Dim IEwindow As IUIAutomationElement, IEtab As IUIAutomationElement
    Dim IEtabs As IUIAutomationElementArray
    Dim tabItemCondition As IUIAutomationCondition
    Dim IEtabPattern As IUIAutomationLegacyIAccessiblePattern
    Dim i As Long
    
    'Create UIAutomation object
    
    Set UIauto = New CUIAutomation
    
    'Get Internet Explorer UIAutomation element
    
    Set IEwindow = UIauto.ElementFromHandle(ByVal IEhwnd)
    
    'Create condition to find tab item controls
    
    Set tabItemCondition = UIauto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TabItemControlTypeId)
 
    'Find all tabs
    
    Set IEtabs = IEwindow.FindAll(TreeScope_Descendants, tabItemCondition)
    
    'Look for the tab which matches the specified tab name
    
    Set IEtab = Nothing
    i = 0
    While i < IEtabs.Length And IEtab Is Nothing
        If LCase(IEtabs.GetElement(i).CurrentName) Like LCase(findTabName) Then Set IEtab = IEtabs.GetElement(i)
        i = i + 1
    Wend
        
    If Not IEtab Is Nothing Then
    
        'Access the legacy pattern of the IE tab, which has the DoDefaultAction method (Click), to click the tab
    
        Set IEtabPattern = IEtab.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
        IEwindow.SetFocus   'optional - brings the IE window to the foreground
        IEtabPattern.DoDefaultAction
    
    Else
    
        MsgBox "IE tab with name '" & findTabName & "' not found"
        
    End If
        
    Set IEtabPattern = Nothing
    Set IEtab = Nothing
    Set IEwindow = Nothing
    Set UIauto = Nothing
    
End Sub

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?

Try this:

Debug.Print HTML.getElementById("product_price").ChildNodes(1).NodeValue

[cp]*[/cp]

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.

Dim HTMLdoc As HTMLDocument
Dim dropdown As HTMLSelectElement
Dim clickEvent As Object


'Assuming IE page has fully loaded at this point
Set HTMLdoc = IE.document
Set clickEvent = HTMLdoc.createEvent("HTMLEvents")
clickEvent.initEvent "click", True, False


Set dropdown = HTMLdoc.getElementById("vendorCodeDropdown")
dropdown.Focus
dropdown.Click
dropdown.value = "SAZJ9"
dropdown.dispatchEvent clickEvent

What's the URL?

Are there any events on the select element, or parent elements? Use your browser's developer tools to find out.

Try this formula:

=IF(B2<TIMEVALUE("00:00:10"),BeepMe(),"")

[cp]*[/cp]

VBScript is more flexible because it can run specific VBA macros, or run its own 'macros'. Without it you would need to use the Workbook_Open event handler to call macros.

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.

[cp]*[/cp]

I can look at this for you. What text do you want as the email reply body text?

[cp]*[/cp]

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).

Public Sub Filter_3_Months()


    Dim theTable As ListObject
    Dim firstMonth As String
    Dim startDate As Date, endDate As Date
        
    With ActiveSheet
        Set theTable = .ListObjects(1)  '1st table on sheet
        firstMonth = .Range("B2").Value
    End With
    
    startDate = DateValue("01 " & firstMonth & " " & Year(Date))
    endDate = DateAdd("m", 3, startDate) - 1
    
    theTable.Range.AutoFilter Field:=5, Criteria1:=">=" & CDbl(startDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(endDate)


End Sub

[cp]*[/cp]

Try adding this line within the With...End With:

.Item.Attachments.Add Range("I9").Value

PS please use CODE tags - the # icon in the message editor.

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.

Dim selectElement As HTMLSelectElement
Dim optionValue As String


Set selectElement = objIE.document.getElementById("ctl00_MainContent_userDDList")
 
optionValue = FindSelectOptionValue(selectElement, "The required visible text")   'Change this string as required
If optionValue <> "" Then
    selectElement.Value = optionValue
Else
    Msgbox "Option text not found"
End If




Private Function FindSelectOptionValue(selectElement As HTMLSelectElement, findOptionText As String) As String


    Dim i As Long
    
    FindSelectOptionValue = ""
    i = 0
    While i < selectElement.Options.Length And FindSelectOptionValue = ""
        'Debug.Print i, selectElement.Item(i).Value & " <" & selectElement.Item(i).Text & ">"
        If StrComp(selectElement.Item(i).Text, findOptionText, vbTextCompare) = 0 Then FindSelectOptionValue = selectElement.Item(i).Value
        i = i + 1
    Wend


End Function

Quote from Oliv-

With IE11 you need to change the call to

Code

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?

[cp]*[/cp]