Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-11-2012, 08:34 AM
CatMan CatMan is offline Windows 7 32bit Office 2010 32bit
Intermediate
 
Join Date: Apr 2012
Posts: 41
CatMan is on a distinguished road
Default 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 09:20 AM.
Reply With Quote
  #2  
Old 12-11-2012, 09:49 PM
CatMan CatMan is offline Windows 7 32bit Office 2010 32bit
Intermediate
 
Join Date: Apr 2012
Posts: 41
CatMan is on a distinguished road
Default

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 09:23 AM.
Reply With Quote
  #3  
Old 12-12-2012, 02:25 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 10,279
macropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the rough
Default

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
[MS MVP - Word]
Reply With Quote
  #4  
Old 12-13-2012, 09:54 PM
CatMan CatMan is offline Windows 7 32bit Office 2010 32bit
Intermediate
 
Join Date: Apr 2012
Posts: 41
CatMan is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 12-13-2012, 10:33 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 10,279
macropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the rough
Default

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
[MS MVP - Word]
Reply With Quote
  #6  
Old 12-14-2012, 09:05 AM
CatMan CatMan is offline Windows 7 32bit Office 2010 32bit
Intermediate
 
Join Date: Apr 2012
Posts: 41
CatMan is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 12-14-2012, 12:34 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 10,279
macropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the roughmacropod is a jewel in the rough
Default

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 "http://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
[MS MVP - Word]
Reply With Quote
Reply

Tags
close window, downloads
Please reply to this thread with any new information or opinions.

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 08: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 11:22 AM
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


All times are GMT -8. The time now is 12:33 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft