Consulting

Results 1 to 5 of 5

Thread: IE Automation File Open IE8/ IE9

  1. #1
    VBAX Newbie
    Joined
    May 2012
    Posts
    3
    Location

    IE Automation File Open IE8/ IE9

    Hi,

    Bit of background. I have some code that navigates to specific page on an internal website where attachments can be downloaded. The website is a front end to an Oracle database which I have access to using ADO via Excel and ODBC in MS Access. The user clicks a button in my Excel tool that runs a query on the database and pulls back a list of unique references. These can then used to build URL's to the page where the attachment can be downloaded. At the same time a query to pull back a list of attachments linked to a specific reference is also run.

    However, I can't workout how to get the file from the database directly, so have gone down the route of getting it from the IE front end. The page appears to use javascript fuctions to retrieve the attachment, there doesn't seem to be a direct URL to the file itself.

    Anyway, I have a solution that works fine for versions of IE that have the 'File Download' Open/ Save popup, but newer versions of IE (I have IE9) have the orange / yellow bar that pops up and I can only get it to work using SendKeys, which I'm not keen on as it's not reliable.

    Basically, using the API function 'FindWindow("#32770", "File Download")' doesn't work for the newer versions of IE and I'd like to find a reliable way of opening the file. My setup is Windows 7 (64 bit) and Excel 2010 (32 bit).

    The user selects the attachment they want from a form, which is passed to this module as 'SelectedAttachment', code is below:

    [VBA]Sub LinkToSPICE(Optional SelectedAttachment As String)

    Dim IE As Object
    Dim IeHandle As Long, FileDownloadHandle As Long, OpenButtonHandle As Long
    Dim AutoMode As Boolean, FileDownloadPopup As Boolean
    Dim Timeout As Date
    Dim strSPICE As String, strLink As String
    Dim PopupGap As Integer, i As Integer

    'sets how much smaller the IE window is than the Excel window
    PopupGap = 30

    'user defined option, in case auto mode fails they can manually open attachment
    If SelectedAttachment = "" Then AutoMode = False Else AutoMode = True

    'create the URL to the web page
    With ThisWorkbook
    strSPICE = .Sheets(shtCapture).Range("Rng_SPICEref")
    strLink = .Sheets(shtRef).Range("SPICE_PREFIX") & strSPICE
    End With

    Set IE = CreateObject("InternetExplorer.application")
    'Get the windows handle for the new IE instance
    IeHandle = IE.hWnd

    If AutoMode = False Then
    'Manual mode - just opens the relevant Spice page, user has to select and open file
    With IE
    .Left = (Application.Left * PointsToPixels) + PopupGap
    .Top = (Application.Top * PointsToPixels) + PopupGap
    .Width = (Application.Width * PointsToPixels) - PopupGap * 2
    .Height = (Application.Height * PointsToPixels) - PopupGap * 2
    .Toolbar = False
    .StatusBar = False
    .MenuBar = False
    .Navigate strLink
    .Visible = True
    End With
    Exit Sub
    Else
    'Auto mode - opens the Spice page, selects the attachment and opens
    With IE
    .Left = (Application.Left * PointsToPixels) + PopupGap
    .Top = (Application.Top * PointsToPixels) + PopupGap
    .Width = (Application.Width * PointsToPixels) - PopupGap * 2
    .Height = 150
    .Toolbar = False
    .StatusBar = False
    .MenuBar = False
    .Navigate strLink
    .Visible = True
    End With

    'just loads a simple modeless progress bar reading 'Locating file'
    Load frm_ProgressBar
    frm_ProgressBar.Show vbModeless
    Application.ScreenUpdating = False

    ' Loop until the page is fully loaded
    Timeout = Now + TimeValue("00:00:20") '-- wait maximum of 20 seconds
    Do While IE.readystate <> 4 Or IE.Busy: DoEvents
    Sleep 250 '--limit loop to reduce CPU load
    If Now > Timeout Then GoTo ErrPageLoad
    Loop

    'This is the textbox, loop through values until the attachment name is found and select
    With IE.document.ALL("ctl00_ContentPlaceHolder1_lstAttach")

    For i = 0 To .Options.length
    'Debug.Print .Options(i).Text
    If InStr(1, .Options(i).Text, SelectedAttachment, 1) <> 0 Then
    .Options(i).Selected = True
    .Value = .Options(i).Value
    Exit For
    End If
    Next i
    If .Value = "" Then GoTo ErrFileDownload

    End With

    '''''Alternative code for the above, could just select the value without looping
    '''''Was going to use the above to populate a form combobox with attachment names
    '''''However I already have the attachment name from SQL query prior to running this sub
    '''''and user selects attachment from that

    'With IE.document.ALL("ctl00_ContentPlaceHolder1_lstAttach")
    '.Value = SelectedAttachment
    'If .Value <> SelectedAttachment Then GoTo ErrFileDownload
    'End With

    'Click on the button that downloads the attachment
    IE.Navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$btnShow','')"
    '''''Alternative code, can use click function below which runs the above javascript
    'IE.document.all("ctl00_ContentPlaceHolder1_btnShow").Click

    FileDownloadPopup = False
    FileDownloadHandle = 0


    'Problem starts here as IE8 / IE9 doesn't appear to have a 'File download' window, instead there is
    'a notification bar (orange / yellow) that appears at the bottom of the IE window
    '
    'Check for type of IE download, if File Download exists, use the code below
    Timeout = Now + TimeValue("00:00:05")
    Do
    FileDownloadHandle = FindWindow("#32770", "File Download")
    DoEvents
    Sleep 250
    Loop Until FileDownloadHandle Or Now > Timeout

    If FileDownloadHandle <> 0 Then FileDownloadPopup = True


    'If 'File Download' window didn't appear, must be newer popup bar type (IE8/IE9)
    If FileDownloadPopup = True Then

    'Find the child open button
    OpenButtonHandle = FindWindowEx(FileDownloadHandle, 0, "Button", "&Open")

    'Just updates my simple userform with 'File downloading' message
    Call frm_ProgressBar.FillVars("Downloading file, please wait...")

    'Bring window in focus and Click the Open button
    SetForegroundWindow (OpenButtonHandle)
    Sleep 1000 'this sleep is required and 600 miiliseconds seems to be the minimum that works
    SendMessage OpenButtonHandle, BM_CLICK, 0, 0
    DoEvents
    Else
    'Take control of IE security banner - Newer versions of IE
    Timeout = Now + TimeValue("00:00:10") '-- wait maximum of 20 seconds
    Do While IE.readystate <> 4: DoEvents
    Sleep 250
    Debug.Print IE.readystate
    If Now > Timeout Then GoTo ErrFileDownload
    Loop

    Call frm_ProgressBar.FillVars("Downloading file, please wait...")

    SetForegroundWindow (IeHandle)
    Sleep 1000
    SendKeys "%O", Wait:=True
    DoEvents
    End If

    'the file should now downloading

    Application.Wait Now + TimeValue("00:00:02")

    Unload frm_ProgressBar
    Application.ScreenUpdating = True

    'Quit IE
    If ThisWorkbook.Sheets(shtRef).Range("Ref_CloseSpiceWeb") = "Y" Then
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    End If

    End If


    Exit Sub

    ErrPageLoad:
    Unload frm_ProgressBar
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    MsgBox "The web page isn't loading, please try again!", vbOKOnly, "Web error"

    Exit Sub

    ErrFileDownload:
    Unload frm_ProgressBar
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    MsgBox "The file could not be located/downloaded, please try again!", vbOKOnly, "Web error"
    Exit Sub

    End Sub[/VBA]


    Also, I have tried identifying the window name that contains the 'Open'/'Save' and 'Save As' buttons on the IE9 download bar, which I've concluded have a different structure to older versions. So far I've found the parent window to be IEFrame (for which I already have the handle for from when I created the instance of IE). I was then hoping to use the FindWindowEx funtion to find the open / save buttons.

    Structure appears to be IEFrame > Frame Notification Bar > DirectUIHWND
    But that's as far as I've got.

    I've also considered generating the javascript and executing it directly to download the file (without the need to navigate through IE), but I'm not really sure how to do that. I've downloaded Firefox Firebug to try and work out what happens when I click the download button but it's a bit too much for my poor brain. Any suggestions would be greatly appreciated.
    Last edited by klanbo; 05-17-2012 at 05:46 AM.

  2. #2
    VBAX Newbie
    Joined
    May 2012
    Posts
    3
    Location
    These are the functions that I have in the workbook. Most of them aren't necessary for what I'm doing here but I just copied from a generic list of functions I have:

    [VBA]Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Public Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Public Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
    Public Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long
    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
    Public Declare Function BringWindowToTop Lib "user32" (ByVal hWnd As Long) As Long
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Public Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
    Public Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
    Public Declare Function SendMessageByString Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
    Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

    Const MAX_PATH = 260

    Public Const BM_CLICK = &HF5
    Public Const WM_SETTEXT = &HC
    Public Const WM_GETTEXT = &HD
    Public Const WM_GETTEXTLENGTH = &HE

    Public Const VK_KEYDOWN = &H0
    Public Const VK_KEYUP = &H2
    Public Const VK_CONTROL = &H11

    Public Const SW_MAXIMIZE = 3
    Public Const SW_SHOWMINIMIZED = 2
    Public Const SW_SHOWNORMAL = 1
    Public Const SM_CXSCREEN As Long = 0
    Public Const SM_CYSCREEN As Long = 1
    Public Const SM_XVIRTUALSCREEN As Long = 0
    Public Const SM_YVIRTUALSCREEN As Long = 1[/VBA]

  3. #3
    VBAX Newbie
    Joined
    May 2012
    Posts
    3
    Location
    Ok, I guess this is a bit of a tricky one so I'll stick with what I've got. I've tweaked the code a little and it works consistently now. I've also changed the code so it checks for both the 'File Download' and the newer 'Frame Notification Bar' in the same loop. Code is below, thought it might be useful to someone.

    [VBA]Sub LinkToSPICE(Optional SelectedAttachment As String)

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
    ''' Links to SPICE website and if an attachment name has been given, opens the file
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

    Dim IE As Object
    Dim IeHandle As Long, FileDownloadHandle As Long, OpenButtonHandle As Long, IePopupBarHandle As Long
    Dim AutoMode As Boolean, FileDownloadClassicPopup As Boolean, DownloadComplete As Boolean
    Dim Timeout As Date
    Dim strSPICE As String, strLink As String
    Dim PopupGap As Integer, i As Integer

    'sets how much smaller the IE window is than the Excel window
    PopupGap = 30

    'user defined option, in case auto mode fails they can manually open attachment
    If SelectedAttachment = "" Then AutoMode = False Else AutoMode = True

    'create the URL to the web page
    With ThisWorkbook
    strSPICE = .Sheets(shtCapture).Range("Rng_SPICEref")
    strLink = .Sheets(shtRef).Range("SPICE_PREFIX") & strSPICE
    End With

    Set IE = CreateObject("InternetExplorer.application")
    'Get the windows handle for the new IE instance
    IeHandle = IE.hwnd

    If AutoMode = False Then
    'Manual mode - just opens the relevant web page, user has to select and open file
    With IE
    .Left = (Application.Left * PointsToPixels) + PopupGap
    .Top = (Application.Top * PointsToPixels) + PopupGap
    .Width = (Application.Width * PointsToPixels) - PopupGap * 2
    .Height = (Application.Height * PointsToPixels) - PopupGap * 2
    .Toolbar = False
    .StatusBar = False
    .MenuBar = False
    .Navigate strLink
    .Visible = True
    End With
    Exit Sub
    Else
    'Auto mode - opens the web page, selects the attachment and opens
    With IE
    .Left = (Application.Left * PointsToPixels) + PopupGap
    .Top = (Application.Top * PointsToPixels) + PopupGap
    .Width = (Application.Width * PointsToPixels) - PopupGap * 2
    .Height = 150
    .Toolbar = False
    .StatusBar = False
    .MenuBar = False
    .Navigate strLink
    .Visible = True
    End With

    'just loads a simple modeless progress bar stating 'Locating File'
    Load frm_ProgressBar
    frm_ProgressBar.Show vbModeless
    Application.ScreenUpdating = False

    ' Loop until the page is fully loaded
    Timeout = Now + TimeValue("00:00:20") '-- wait maximum of 20 seconds
    Do While IE.readystate <> 4 Or IE.Busy: DoEvents
    Sleep 250 '--limit loop to reduce CPU load
    If Now > Timeout Then GoTo ErrPageLoad
    Loop

    'This is the textbox, loop through values until the attachment name is found and select
    With IE.document.ALL("ctl00_ContentPlaceHolder1_lstAttach")

    For i = 0 To .Options.length
    If InStr(1, .Options(i).Text, SelectedAttachment, 1) <> 0 Then
    .Options(i).Selected = True
    .Value = .Options(i).Value
    Exit For
    End If
    Next i
    If .Value = "" Then GoTo ErrFileDownload

    End With

    'Click on the button that downloads the attachment
    IE.Navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$btnShow','')"
    '''''Alternative code, can use click function below which runs the above javascript
    'IE.document.all("ctl00_ContentPlaceHolder1_btnShow").Click

    DownloadComplete = False
    FileDownloadClassicPopup = False
    FileDownloadHandle = 0
    IePopupBarHandle = 0

    Sleep 600

    'Check for both File Download window and the new type popup 'Frame Notification Bar' as found in IE8/9
    Timeout = Now + TimeValue("00:00:20")
    Do While DownloadComplete = False

    IePopupBarHandle = FindWindowEx(IeHandle, 0, "Frame Notification Bar", vbNullString)
    'probably don't need the line below as the above window should be fine
    IePopupBarHandle = FindWindowEx(IePopupBarHandle, 0, "DirectUIHWND", vbNullString)

    If IePopupBarHandle <> 0 And IE.readystate = 4 Then
    DownloadComplete = True
    Else
    FileDownloadHandle = FindWindow("#32770", "File Download")
    If FileDownloadHandle <> 0 Then DownloadComplete = True: FileDownloadClassicPopup = True
    End If
    DoEvents
    If Now > Timeout Then GoTo ErrFileDownload
    Sleep 250
    Loop

    'Just updates my simple userform with 'File downloading' message
    Call frm_ProgressBar.FillVars("Downloading file, please wait...")

    'If the 'File Download' window appeared, click on the open button
    If FileDownloadClassicPopup = True Then

    'Find the child open button
    OpenButtonHandle = FindWindowEx(FileDownloadHandle, 0, "Button", "&Open")

    'Bring window in focus and Click the Open button
    SetForegroundWindow (OpenButtonHandle)
    Sleep 1000 'this sleep is required and 600 miiliseconds seems to be the minimum that works
    SendMessage OpenButtonHandle, BM_CLICK, 0, 0
    DoEvents
    Else
    'Take control of IE security banner - Newer versions of IE with popup bar
    SetForegroundWindow (IeHandle)
    Sleep 1000
    SendKeys "%O", Wait:=True
    DoEvents
    End If

    'the file should now be opening

    Application.Wait Now + TimeValue("00:00:02")

    Unload frm_ProgressBar
    Application.ScreenUpdating = True

    'Quit IE
    If ThisWorkbook.Sheets(shtRef).Range("Ref_CloseSpiceWeb") = "Y" Then
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    End If

    End If

    Exit Sub

    ErrPageLoad:
    Unload frm_ProgressBar
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    MsgBox "The web page isn't loading, please try again!", vbOKOnly, "Web error"

    Exit Sub

    ErrFileDownload:
    Unload frm_ProgressBar
    On Error Resume Next
    IE.Quit
    IE = Nothing
    On Error GoTo 0
    MsgBox "The file could not be located/downloaded, please try again!", vbOKOnly, "Web error"

    Exit Sub

    End Sub[/VBA]

  4. #4
    Hi klanbo,
    I have the same problem as You had last year. Did You get any solution that is not documented here?

  5. #5
    VBAX Regular
    Joined
    Jul 2009
    Posts
    14
    Location
    klanbo,
    Thanks for the above! It looks like it may solve many peoples issues. I am having a little problem following the code as I am not familiar with the workbook layout and what the different cells contain, nor am I familiar with spice website.
    Could you please attach a sample workbook and the link(s) being used in the sample?

    Thanks very respectfully!
    Bryce


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •