Hi mate I tried popping this into the on click VBA action but shows an error "Complie Error" Expected end sub
Close Internet Explorer in VBA (1 Viewer)
- Thread starter stu_c
- Start date
how very oddCould not have copied it correctly then?
There is an End Sub at the bottom of that code.
I have just copied that code into a test DB and compiled and ran it, without any issues. ?
Code:
Private Sub BtnIEClose_Click()
Sub KillProcess(strProcess As String)
' ProcessKillLocal.vbs
' Sample VBScript to kill a program
' Author Guy Thomas http://computerperformance.co.uk/
' Version 2.7 - December 2010
' Modified for VBA Paul Steel 02/07/2022
' ------------------------ -------------------------------'
Dim objWMIService As Object, objProcess As Object, colProcess As Object
Dim strComputer As String, strProcessKill As String
Dim blnKilled As Boolean
strComputer = "."
strProcessKill = "'" & strProcess & "'"
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = " & strProcessKill)
On Error Resume Next
For Each objProcess In colProcess
blnKilled = True
objProcess.Terminate
Next
If blnKilled Then
MsgBox "Just killed all processes for " & strProcessKill
End If
End Sub
- Local time
- Today, 13:42
- Joined
- Feb 28, 2001
- Messages
- 27,146
Although I don't see it in the presented code segment, the error about END SUB occurs if some previous IF, FOR, or WITH was improperly terminated. Any of those syntax elements start a "code block" and if the block is improperly terminated then the compiler complains because it was expecting closure of something else. Since that code segment appears to be something in a class module, odds are that other subs and/or functions are also there. The "Expected End Sub" message doesn't highlight this code because it is an error of omission so there is nothing to which the compiler can point, nothing to turn red. Unlike EXIT SUB (which CAN occur inside another type of block), END SUB is a compiler directive that must occur in a specific level (at "block depth zero.") Yes, END SUB implies an EXIT SUB, but in this case it is not relevant.
What I would do is look at the PREVIOUS subs in that same class module to see if there is an improperly terminated IF, LOOP, DO, UNTIL, etc. or a FOR loop. It is possible that when you entered this code you might have accidentally clobbered a previous routine's END SUB. Another test would be to cut/paste this routine into a text file so you can easily get it back, erase the event property that links to the routine, then try to compile it. If the problem WAS that you clobbered the END SUB for some other routine, you'll get the same error. (Or do this to a COPY of the DB so it will be trivial to reinstate this code after the test.)
What I would do is look at the PREVIOUS subs in that same class module to see if there is an improperly terminated IF, LOOP, DO, UNTIL, etc. or a FOR loop. It is possible that when you entered this code you might have accidentally clobbered a previous routine's END SUB. Another test would be to cut/paste this routine into a text file so you can easily get it back, erase the event property that links to the routine, then try to compile it. If the problem WAS that you clobbered the END SUB for some other routine, you'll get the same error. (Or do this to a COPY of the DB so it will be trivial to reinstate this code after the test.)
Hello, Stu. Here's a little sample code that can help you grab the IE window, I used it in some excel project a few years ago, it should work the same in access. Here's the code:
By the way, this is outside the scope of your question, but I'd recommend a more modern approach to this. Using NodeJS, you can download the Puppeteer library and easily code this automation using Javascript to control Chromium. The resulting NodeJS project can be compiled into an executable that reads an external JSON file in your computer and you can make Access write that JSON file. That way the entire solution would look like this: User in Access runs a macro with a button >> Access fills the external JSON file, then triggers the execution of the executable >> The executable reads the JSON file >> The executable runs Chromium and fills the form with the data in the JSON.
Code:
Option Explicit
#If VBA7 Or Win64 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As LongPtr)
Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If
Public Function FUNCCompleteForm()
Dim URL As String: URL = "https://www.access-programmers.co.uk/forums/members/"
Dim HTMLdoc As HTMLDocument
Dim oHTML_Element As IHTMLElement
Dim IE As InternetExplorer: Set IE = Get_IE_Window(URL)
If IE Is Nothing Then Set IE = New SHDocVw.InternetExplorer
With IE
.Visible = True
.Navigate URL
SetForegroundWindow .hwnd
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 200: Wend
While .Document.ReadyState <> "complete": DoEvents: Sleep 200: Wend
Set HTMLdoc = .Document
End With
' YOUR CODE TO FILL WEB STUFF
For Each oHTML_Element In HTMLdoc.getElementsByTagName("input")
Select Case oHTML_Element.Name
Case Is = "username"
oHTML_Element.setAttribute "value", Range("myRange") 'THIS IS EXCEL, ADAPT TO ACCESS
End Select
Next
Set HTMLdoc = Nothing
Set IE = Nothing
SendKeys "{NUMLOCK}", True 'if it turns off your numlock, don't ask me why it does that
End Function
Private Function Get_IE_Window(URL As String) As SHDocVw.InternetExplorer
'Looks for a window of tab opened in the specified URL, can be http or https or nothing
'If it finds it, it returns the browser as an IE object, otherwise it returns nothing
Dim Domain As String
Dim Shell As Object
Dim IE As SHDocVw.InternetExplorer
Dim i As Variant 'Variant to index Shell.Windows.Item() array
Dim p1 As Integer, p2 As Integer
p1 = InStr(URL, "://")
If p1 = 0 Then
p1 = 1
Else
p1 = p1 + 3
End If
p2 = InStr(p1, URL, "/")
If p2 = 0 Then p2 = Len(URL) + 1
Domain = Mid(URL, p1, p2 - p1)
Set Shell = CreateObject("Shell.Application")
i = 0
Set Get_IE_Window = Nothing
While i < Shell.Windows.Count And Get_IE_Window Is Nothing
Set IE = Shell.Windows.Item(i)
If Not IE Is Nothing Then
'Debug.Print IE.LocationURL, IE.LocationName
'Debug.Print TypeName(IE)
If TypeOf IE Is SHDocVw.InternetExplorer And InStr(IE.LocationURL, "file://") <> 1 Then
If InStr(IE.LocationURL, Domain) > 0 Then
Set Get_IE_Window = IE
End If
End If
End If
i = i + 1
Wend
End FunctionBy the way, this is outside the scope of your question, but I'd recommend a more modern approach to this. Using NodeJS, you can download the Puppeteer library and easily code this automation using Javascript to control Chromium. The resulting NodeJS project can be compiled into an executable that reads an external JSON file in your computer and you can make Access write that JSON file. That way the entire solution would look like this: User in Access runs a macro with a button >> Access fills the external JSON file, then triggers the execution of the executable >> The executable reads the JSON file >> The executable runs Chromium and fills the form with the data in the JSON.
