Solved: Help with excel-Macro to open file from http-link

[Tobias F]

Hello All,

Im struggling with a Macro that should:

1. Launch a http-link to a document in our PDM-system (link is not of .office type, however the file an excel document)
2. Wait for "Connect to vanillapdmlink9.skf.net" login-window to appear
3. Enter login credentials
4. Wait for the "File Download" dialog-window to appear
5. Choose to Open the file in the "File Download" dialog-window

The code below is extremly simple and works to do the above, however its extremly unreliable and not to secure since the sendkeys often trigger to late due to latency problems.


[VBA]
Sub myWebOpenPW()
'Open site if it requires a PassWord Model!
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
'Go to this Web Page!
IE.Navigate "To http adress ending with AttachmentsURLRedirectServlet?oid=VR:wt.doc.WTDocument:4217722"

'wait for the "Connect to vanillapdmlink9.skf.net" popup window to appear
Application.Wait (Now + TimeValue("0:00:02"))
IE.Visible = False
SendKeys "USERNAME", True
SendKeys "{TAB}", True
SendKeys "PASSWORD", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
'Wait for the "File Download" window to appear
Application.Wait (Now + TimeValue("0:00:06"))
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
End Sub
[/VBA]

I have been trying to use the FindWindow function in a loop to wait for the 2 windows, im having problems to find the windows "Connect to vanillapdmlink9.skf.net" and the "File Download" According to:

[VBA]
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal _
lpClassName As String, ByVal lpWindowName As String) As Long

'Do
'thewindow = FindWindow(vbNullString, "Connect to vanillapdmlink9.skf.net")
DoEvents
'Loop Until thewindow <> 0
[/VBA]
The above code always seem to hang and will not exit. The questions i have is:

Is it the right approach to use Findwindow?
How do i write the code to find the different pop-up windows, one is the IE-login window, the other the excel-"file download"? what am i doing wrong?

Any tips or hints or other ways to solve this would be appriciated!

/Tobias