klanbo
05-17-2012, 05:07 AM
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:
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
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.
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:
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
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.