#1
|
|||
|
|||
Close 'downloads' window with VBA
Hi forum, thanks to all for taking the time to read this.
I need to closed the File Explorer (FE) 'downloads' window and an Internet Explorer (IE) window using VBA. I am using windows 7. The downloads folder is generally located here: "C:\Users\User\Downloads". I can open it like this, but how do you close it? I also need to close an IE window. Both the FE an IE windows open as a result a process that is VBA launched using ShellExicute, which does not support any hand shaking between the VBA process and the ShellExicute process because ShellExicute does not support variable passing, unless someone knows better. Closing all FE windows would suffice but hopefully there is a way to close the downloads folder FE. Code to open downloads folder: Code:
Shell "explorer C:\Users\User\Downloads" Last edited by CatMan; 12-12-2012 at 10:20 AM. |
#2
|
|||
|
|||
I did a little research on how to close an IE window and found a site that offers a decent solution, except it does not work 100%. I am not sure if it is ok to include a link to another forum so I'll leave it out unless someone can confirm it's ok to include. The first block of code closes all IE windows except for those containing a user defined string (the code refers to this as 'blocked' (the string 'Etreme' is blocked).
Underneath this code is a modifed version (of the code) that I tweaked to do the opposite, close only windows containing a user defined string. To test this code, open 2 IE windows, use one to search for 'testing' and run the code. the testing IE will close and the other will remain open. It works but the looping never stops. I guess the do-while is not setup correctly. Does anyone know how to fix this? BTY, as I was checking this out it appears the classic way to approach this is to have the process responsible for launching the IE window pass a PID back to VBA and then to use a simple API to kill that PID. I am using shellexicute to send a URL to a datamart and that process opens IE. There is no way to pass a variable back using shellexicute so the PID approach won't work, unless someone knows better. This code gets around the lack of hand shaking nicely. This is the starting point, please focus on code below this Code:
Private Sub CloseWindow() Dim lngWindow As Long, lngWindowLast As Long Dim strClassSought As String: strClassSought = "IEFrame" 'type of window to close Dim strTitleBlocked As String: strTitleBlocked = "Xtreme" 'windows to exclude Dim strClass As String, strTitle As String Dim i As Integer lngWindow = GetWindow(Application.hwnd, GW_HWNDFIRST) lngWindowLast = GetWindow(Application.hwnd, GW_HWNDLAST) 'now loop through all windows Do While lngWindow <> lngWindowLast 'create a buffer for the class name and the window title strClass = Space(100) strTitle = Space(100) 'get the class name and the window title GetWindowText lngWindow, strTitle, 100 GetClassName lngWindow, strClass, 100 'check whether this window matches the criteria If InStr(strClass, strClassSought) <> 0 And InStr(strTitle, strTitleBlocked) = 0 Then 'post a message to the window to close itself PostMessage lngWindow, WM_CLOSE, 0&, 0& 'start all over lngWindow = GetWindow(Application.hwnd, GW_HWNDFIRST) Else 'get the next window lngWindow = GetWindow(lngWindow, GW_HWNDNEXT) End If C1 = C1 + 1: Application.StatusBar = C1: DoEvents Loop End Sub This code closes only windows containing a user defined string Code:
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long Const WM_CLOSE = &H10 Const GW_HWNDFIRST = 0 Const GW_HWNDLAST = 1 Const GW_HWNDNEXT = 2 Private Sub CloseWindow() Dim lngWindow As Long, lngWindowLast As Long Dim strClassSought As String: strClassSought = "IEFrame" 'type of window to close Dim strTitleToClose As String: strTitleToClose = "testing" 'windows to include Dim strClass As String, strTitle As String Dim i As Integer lngWindow = GetWindow(Application.hwnd, GW_HWNDFIRST) lngWindowLast = GetWindow(Application.hwnd, GW_HWNDLAST) 'now loop through all windows Do While lngWindow <> lngWindowLast 'create a buffer for the class name and the window title strClass = Space(100) strTitle = Space(100) 'get the class name and the window title GetWindowText lngWindow, strTitle, 100 GetClassName lngWindow, strClass, 100 'check whether this window matches the criteria If InStr(strClass, strClassSought) <> 0 And InStr(strTitle, strTitleToClose) <> 0 Then 'post a message to the window to close itself PostMessage lngWindow, WM_CLOSE, 0&, 0& 'start all over lngWindow = GetWindow(Application.hwnd, GW_HWNDFIRST) Else 'get the next window lngWindow = GetWindow(lngWindow, GW_HWNDNEXT) End If C1 = C1 + 1: Application.StatusBar = C1: DoEvents Loop End Sub Last edited by CatMan; 12-12-2012 at 10:23 AM. |
#3
|
||||
|
||||
Hi CatMan,
Including links from other sites where you've found relevant content is OK. Is there aparticular reason you're accessing the folder via an Explorer shell, rather than via the File System Object or something such?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Hi macropod, yes, I am using ShellExicute to send a URL to firefox, the url triggers some IT process that I have no control over, it sends data from a datamart and saves it to Excel in the downloads folder (the url does all that). The url does a good job (seems robust) of putting an excel file in the downloads folder so all I need to do is open it and copy the worksheet into the host Excel workbook. The host worksheet then has fresh data on which various macro's inside the host workbook operate on. I have all this working but I would like to clean up after the url exicutes, that is, close the firefox and downloads windows to make the process more transparent (and friendly) to the user.
|
#5
|
||||
|
||||
I'm not sure what the url stuff has to do with the Explorer shell to the downloads folder, as you don't need that to get the file. You can use the File System Object (or even a Dir command) to test for the file's presence and, when it's available, open it, without ever going near Shell. It seems to me that, by using Shell, you're just making extra work for yourself.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Shellexicute sends a url to firefox resulting in a fresh data pull into firefox and then saves the data as an excel workbook in the downloads folder. If the url is not issued then no data is pulled and no workbook is built.
|
#7
|
||||
|
||||
Hi CatMan,
You seem to be using two Shell statements - one for Firefox (though you previously said it was an IE window) and the other for Windows Explorer. So far, though, you've only posted a brief snippet for the Windows Explorer shell, and that's what I've been concentrating on. And what I've been saying is that you don't need the Windows Explorer Shell - you can get the file using other methods. You quite possibly don't need the IE/Firefox shell, either, as IE can be automated without it. Just a brief example: Code:
Sub Get_URL_Data() 'References to Internet Explorer & Microsoft HTML required Application.ScreenUpdating = False Dim Browser As SHDocVw.InternetExplorer Dim HTMLDoc As MSHTML.HTMLDocument StrData As String Set Browser = New SHDocVw.InternetExplorer 'Open the web page Browser.navigate "https://www.msofficeforums.com/excel-programming/15359-close-downloads-window-vba.html" Do While Browser.Busy DoEvents Loop Set HTMLDoc = Browser.Document Do While Browser.Busy DoEvents Loop 'Get the data StrData = HTMLDoc.body.innerText MsgBox StrData 'Close the browser Browser.Quit Set HTMLDoc = Nothing: Set Browser = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
close window, downloads |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to find the path to the downloads folder when it is different for each user | CatMan | Excel Programming | 2 | 12-07-2012 09:59 PM |
Outlook continues to run after I close it | Shawn76 | Outlook | 1 | 08-23-2010 12:30 AM |
Outlook 2007 downloads all emails everytime i send/receive | helpmeplease | Outlook | 0 | 12-28-2008 12:22 PM |
Is there an exit on close ? | mhdadmh | Word | 0 | 10-12-2006 04:28 PM |
Mass attachment downloads? | dpool2002 | Outlook | 1 | 06-21-2006 02:11 PM |