VBA Getting control of open internet window with specific URL

[Sense]

I have tried the first code 'stand alone', So without any of my code in it. I adjusted it to internetexplorer.application and added a visible=true line to check if it created a new instance.

 Function MyFunctionName()
On Error GoTo Err_Handler
'...other unrelated code here if needed
  'Start IE
    On Error Resume Next
    Dim oIE As Object
    Set oIE = GetObject(, "InternetExporer.Application")    'Bind to existing instance of Internet Explorer
 
    If Err.Number <> 0 Then    'Could not get instance of Internet Explorer, so create a new one
        Err.Clear
        On Error GoTo Err_Handler
        Set oIE = CreateObject("InternetExplorer.Application")
        bIEOpened = False
    Else    'IE was already running
        bIEOpened = True
    End If
    
    On Error GoTo Err_Handler
oIE.Visible = True
'...more unrelated code here
  Set oIE = Nothing
Exit_Handler: Exit Function
Err_Handler: MsgBox "Error " & Err.Number & " in MyFunctionName routine: " & Err.Description
Resume Exit_Handler
End Function

1. Running with internet explorer closed

runs getobject can't find object
checks err.number
Creates object
has made a new ie object



2. Running with internet explorer opened

runs getobject can't find object
checks err.number
Creates object
has made a new ie object

So apparently it can make a new instance but cannot find an open one.

[isladogs]

It seems that, unlike applications such as Word, Outlook etc, IE ALWAYS opens a new instance

Slightly shorter version of same code

 Function MyFunctionName()On Error GoTo Err_Handler


'...other unrelated code here if needed
  'Start IE
    On Error Resume Next
    Dim oIE As Object
    Set oIE = GetObject(, "InternetExporer.Application")    'Bind to existing instance of Internet Explorer
'    Debug.Print Err.Number & " " & Err.Description
    If Err.Number = 429 Then   'Could not get instance of Internet Explorer, so create a new one
        Err.Clear
        On Error GoTo Err_Handler
        Set oIE = CreateObject("InternetExplorer.Application")
    End If
    
    bIEOpened = True
    
    On Error GoTo Err_Handler
    
    oIE.Visible = True
    
'...more unrelated code here


  Set oIE = Nothing
  
Exit_Handler:
    Exit Function
    
Err_Handler:
  '  If Err = 429 Then Resume Next
    MsgBox "Error " & Err.Number & " in MyFunctionName routine: " & Err.Description
    Resume Exit_Handler
End Function

[Sense]

It seems that, unlike applications such as Word, Outlook etc, IE ALWAYS opens a new instance

Perhaps.. the funny thing is that in excel my original code works without a problem... I don't get it

[Sense]

A function is required if need to return a value to calling procedure. Also, queries and Access controls can see functions but not subs.

Okay thanks! So it's better to use functions. Is there any reason why I should use subs instead of Functions?

[Sense]

Okay I understand now that shell.application is able to find an open browser even though it's in fact the windows explorer. InternetExplorer.application is unable to do that.
So I would say have to get the shell.application object working in access.

[isladogs]

Okay thanks! So it's better to use functions. Is there any reason why I should use subs instead of Functions?

Some developers only use functions even where a sub will work - the idea being it gives maximum flexibility
Other developers of course think that is totally inappropriate.

The code below allows you to easily use the Shell command in Access
Place the code in a standard module

Option Compare Database
Option Explicit

'API declarations for 32-bit/64-bit Access
'###############################################
#If VBA7 Then 'use PtrSafe & LongPtr
    Private Declare PtrSafe Function apiShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" _
        (ByVal hWnd As LongPtr, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) _
        As Long
#Else '32-bit Office
    Private Declare Function apiShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" _
        (ByVal hWnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) _
        As Long
#End If
'###############################################

Public Sub ShellEx(ByVal Path As String, Optional ByVal Parameters As String, Optional ByVal HideWindow As Boolean)

    If Dir(Path) > "" Then
        apiShellExecute 0, "open", Path, Parameters, "", IIf(HideWindow, 0, 1)
    Else
        MsgBox "Can't find program"
    End If

End Sub

Here are 3 examples of it in use that you can try

Public Function OpenMagnifier()
    ShellEx "c:\windows\system32\magnify.exe", , 0
End Function


Public Function RunOSK()
'opens on screen keyboard 
    apiShellExecute 0, vbNullString, "osk.exe", vbNullString, "C:\", 1
   ' ShellEx "c:\windows\system32\osk.exe", , True
End Function


Public Function OpenTabTip()
'opens tablet screen keyboard 
   ShellEx "C:\Program Files\Common Files\Microsoft Shared\ink\TabTip.exe", , True
End Function

[Sense]

After some more research I conclude my code and yours ofcourse should work. The problem may have to do with the version of access, company restriction or anything environmental.
Now ranman256 had this idea of using a internetbrowser control in a form. I tried that and it works so I'll use it for this case.

I will take a look at the Shell code soon.


Thanks all for your help!

[isladogs]

Strange. Can't see a post from Ranman in this thread so forgive the follow up question
There are two types of web browser control in Access

The first is on the design toolbar and is fairly simple in terms of what it can do. Basically opens a web page externally
Then there is the ActiveX version which is much more powerful, allowing you to open almost any external document (PDF/DOCX/XLSX etc) as well as web pages in a browser window within Access.

If the former, how is it better than what you had?
If the latter, do note that some organisations and individuals do not allow the use of ActiveX controls due to potentials risks with rogue controls

[Sense]

Ahhh you're right it didn't solve anything.. Tomorrow I'll check your shell code