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

[Jan Karel Pieterse]

No, you should use an alternative method altogether I think.

Here is a function that will download a file using http:

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Private Sub DownloadFile(strWebFilename As String, strSaveFileName As String)
' Download the file.
URLDownloadToFile 0, strWebFilename, strSaveFileName, 0, 0
End Sub

You call the function called "DownloadFile" with two string arguments: the URL to the file you need and the path and filename of the location where the downloaded file must be saved.

I am not sure however how this will work in conjunction with the logging in.

[Tobias F]

Thanks for the reply, im unsure how to use the urldownloadtofile. The url is a directlink to the latest iteration of the object in our PDMsystem.
When calling this link you are immediatly prompted for log-in and pass, if you have acess to the area and if the login info is entered correctly, you get the prompt "File Download"(Open, Save as, Cancel) after the connection is established. Its basicly an automated "open_file_from_web" behaviour i am after with this macro. The files i am going to open contain macros that are doing some calculation and sending the output to another application.

I tweaked my code a littlebit now and it`s been working pretty good so far. Finally got the Findwindow-loop to wait for the "login-" and "File Download" windows. I`m still using sendkeys, but it seem to be more stable.

Here`s the code so far:

[vba]
Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function SetActiveWindow Lib "user32.dll" _
(ByVal hwnd As Long) As Long
Private Declare Function BringWindowToTop Lib "user32" _
(ByVal hwnd As Long) As Long
' Windows keyboard message constants:
Const WM_SETTEXT = &HC
Const WM_SYSKEYDOWN = &H104&
Const BM_CLICK = &HF5
-----------------------------------------------------------------------
Sub myWebOpenPW()
'Open site if it requires a PassWord Model!

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False

'GoTo PDMlink
IE.Navigate
"hppp://vanillapdmlink9.skf.net........WTDocument:4217722"

'Find IE Window
Dim Download As Long
Download = FindWindow(vbNullString, "Microsoft Internet Explorer")
If Download = 0 Then
MsgBox "Cannot Open Browser"
Exit Sub
End If

Application.Wait (Now + TimeValue("0:00:01"))

'Wait for login window
Login = WaitForWindow("Connect to vanillapdmlink9.skf.net", "")

'Put the focus on Login Window
'Bring Login to the top of window stack
'Make Login the active window
Call BringWindowToTop(Login)
Call SetActiveWindow(Login)

Application.Wait (Now + TimeValue("0:00:01"))

'Sending login information!
SendKeys "PASSWORD", True
SendKeys "{TAB}", True
SendKeys "LOGIN", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True

Application.Wait (Now + TimeValue("0:00:01"))

'Wait for window "File Download"
Spara = WaitForWindow("File Download", "")

'Put the focus on Spara Window
'Bring Spara to the top of window stack
'Bake Spara the active window
Call BringWindowToTop(Spara)
Call SetActiveWindow(Spara)

'Simulates Alt + S, Opens the file
PostMessage Savehwnd, WM_SYSKEYDOWN, vbKeyO, 2 ^ 29

End Sub
----------------------------------------------------------------------

Private Function WaitForWindow(strCaption As String, Optional strClassName) As Long
' Runs a loop, waiting for a window to appear

Dim WinWnd As Long
If IsMissing(strClassName) Then strClassName = vbNullString

Do

WinWnd = FindWindow(strClassName, strCaption)

If WinWnd <> 0 Then
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
Else: GoTo 100
End If

Loop Until WinWnd <> 0

WaitForWindow = WinWnd

100
End Function
[/vba]


Next steps would be to add alerts to the users to "check connection" etc if loops are taking to long. I would also like to get around the hardcoded sendkeys and get password stored in the registry.

Any comments or improvements of current code is appriciated.

[lucas]

You guys know you can select your code and hit the vba button to format it for easier reading?

[Tobias F]

Done!

[lucas]

Hi Tobias, if you have your solution please mark your thread solved using the thread tools at the top of the page....

Welcome to the board.