Login to Website and Click Link

[Jannis95]

Hi, i have an Excel VBA Code that allow me to Login to a Website. That works fine.
But then on the new page that loaded after the Login, i want to click on a link with the Name "Gesamtausgabe".
Can someone help me and finish the script with that functions. It would be very nice.

Greetings
( Sorry for my bad english :crying: )



Option Explicit

Public Sub Login()


'make sure you add references to Microsoft Internet Controls and
'Microsoft HTML Object Library. Code will NOT run otherwise.
Dim ie As InternetExplorer
Dim html As HTMLDocument
Dim IeApp As InternetExplorer
Dim Link As Object
Dim ieDoc As Object
Dim ieAnchors As Object
Dim Anchor As Object
Dim ElementCol As Object
Dim Element As HTMLLinkElement
Dim objIE As SHDocVw.InternetExplorer 'Microsoft Internet Controls
Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim login As Object
Dim ele As Object

Set objIE = New SHDocVw.InternetExplorer

With objIE
.navigate "LINK TO THE WEBSITE" ' Main page
.Visible = 1
Do While .readyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

'set user name and password
Set htmlDoc = .document
Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
Do While htmlDoc.readyState <> "complete": DoEvents: Loop
For Each htmlInput In htmlColl
If htmlInput.Name = "email" Then
htmlInput.Value = "EMAIL"
Else
If htmlInput.Name = "passwort" Then
htmlInput.Value = "PASSWORD"
End If
End If
Next htmlInput

'click login
objIE.document.forms(0).submit
Do While .readyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Set htmlDoc = Nothing
Set objIE = Nothing


End With


End Sub

[mancubus]

welcome to the forum.

this may give you a start:
http://www.mrexcel.com/forum/excel-questions/322565-visual-basic-applications-click-link-2.html#post1584434

adapted and shortened code could be like:


With objIE
.Navigate "LINK TO THE WEBSITE" ' Main page
.Visible = True
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Set htmlColl = .document.getElementsByTagName("INPUT")
For Each htmlInput In htmlColl
If htmlInput.Name = "email" Then htmlInput.Value = "EMAIL"
If htmlInput.Name = "passwort" Then htmlInput.Value = "PASSWORD"
Next htmlInput

.document.forms(0).submit
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Set ElementCol = .document.getElementsByTagName("a")
For Each Element In ElementCol
If Element.innerHTML = "Gesamtausgabe" Then
NewURL = Element
Exit For
End If
Next Element

.Navigate NewURL
.Visible = True
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))
End With


work on it. good luck!

[Jannis95]

Hello mancubus, thanks for your help! :hi:
It works thanks!

The Link opens a pdf.
Is there a way to automaticacly save that pdf in a specifc path on my harddrive?

[mancubus]

you are welcome.

giybf! :)

Google "Declare Function URLDownloadToFile"

here is something to play with.
work on this line specifically:
DownloadFile NewURL, "C:\MyFiles\FromWeb\" & Replace(NewURL, "%20", " ")




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

Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function


Sub vbax_53699_LogInWebDownloadPDF()
'make sure you add references to Microsoft Internet Controls and
'Microsoft HTML Object Library. Code will NOT run otherwise.

'Dim ie As InternetExplorer
'Dim html As HTMLDocument
'Dim IeApp As InternetExplorer
'Dim Link As Object
'Dim ieDoc As Object
'Dim ieAnchors As Object
'Dim Anchor As Object
'Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
'Dim login As Object
'Dim ele As Object

Dim objIE As SHDocVw.InternetExplorer 'Microsoft Internet Controls
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim htmlInput As MSHTML.HTMLInputElement
'Dim ElementCol As Object
Dim ElementCol As MSHTML.IHTMLElementCollection
Dim Element As HTMLLinkElement

Set objIE = New SHDocVw.InternetExplorer

With objIE
.Navigate "LINK TO THE WEBSITE" ' Main page
.Visible = True
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Set htmlColl = .document.getElementsByTagName("INPUT")
For Each htmlInput In htmlColl
If htmlInput.Name = "email" Then htmlInput.Value = "EMAIL"
If htmlInput.Name = "passwort" Then htmlInput.Value = "PASSWORD"
Next htmlInput

.document.forms(0).submit
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Set ElementCol = .document.getElementsByTagName("a")
For Each Element In ElementCol
If Element.innerHTML = "Gesamtausgabe" Then
NewURL = Element
Exit For
End If
Next Element

.Navigate NewURL
.Visible = True
Do While .ReadyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))
End With

DownloadFile NewURL, "C:\MyFiles\FromWeb\" & Replace(NewURL, "%20", " ") 'change folder name to suit

End Sub

[Jannis95]

Hi, im so happy that you help me! :)

Is that right? I must define the variable:


Dim DownloadFile As Object

[mancubus]

no. it's a function.
it takes two parameters: first is the URL of the file to be downloaded and second is the full name of the file when downloaded.

i forgot copying it. sorry.

added to the previous code.

you should check if the NewURL variable fully reflects the adress of the file.
spaces in file names are replaced with %20". so replace them bach to spaces via Replace(NewURL, "%20", " ")
C:\MyFiles\FromWeb\ is the folder to download the file. change to suit.

[Jannis95]

14359

Now i get this error.
I know its german :banghead:

[mancubus]

it looks a compile error.
check url is correct.

a comprehensive url download function can be found at:
www.cpearson.com/Excel/DownloadFile.aspx (http://www.cpearson.com/Excel/DownloadFile.aspx).

[Jannis95]

the URL is correct.
I dont know whats wrong :dunno

[mancubus]

declare function is for 32Bit office.

then check the file name...

an example:
DownloadFile NewURL, "C:\MyFiles\FromWeb\MyNew.pdf"

or try chip pearson's code. i'm using it for years with success.

[snb]

You can simply 'download' a PDF file immediately.

Maybe it would be better to ask this question in the forum office-loesung.de/p/.z.B.


Sub M_snb()
CreateObject("WScript.Shell").Run "http://www.snb-vba.eu/bestanden/krant001.PDF"
End Sub

[Jannis95]

German support yay

Yes, so if you want you can help me for a moment. I'm not exactly familiar with vba right now.


I currently have to see the code as above. He redirects me directly to a page after logging in. From there he clicks a link that opens a PDF. I would now like to save this PDF automatically. Could you tell me exactly how to do it - at best just extend the code from above? I would be very grateful to you!


Greetings, Jannis

[snb]

Right-click the link to the PDF file, then you will see where it is located.

You can then simply enter this address with my sample code.

[mancubus]

heyya...

English is also a germanic language...


:D

[snb]

Stimmt ganz genau :whistle:

[Jannis95]

heyya...

English is also a germanic language...


:D

Okay lets Switch to english again... :D

It still don't work :( I get this error:

[mancubus]

success, as always...



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

Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function


Sub test_url_download_variables()
Dim FileURL As String
Dim DownloadedFileName As String

FileURL = http://www.cpearson.com/Zips/FindAll.zip
DownloadedFileName = "Z:\VBAProjectFiles\TestFile.zip"

DownloadFile FileURL, DownloadedFileName
End Sub

'or simply:
Sub test_url_download()
DownloadFile "http://www.cpearson.com/Zips/FindAll.zip", "Z:\VBAProjectFiles\TestFile.zip"
End Sub


[ URL ] [ /URL ] tags are automatically inserted as per forum settings. they should be cleared in the code.

FileURL = "http://www.cpearson.com/Zips/FindAll.zip"

[mancubus]

Stimmt ganz genau :whistle:

Google translates this as "exactly" :devil2:


the family tree is:

...Indo-European languages
......Germanic languages
.........West Germanic
............Anglo-Frisian languages
...............English

thanks wikipedia :rofl:

[snb]

@Mancubus:

That's exactly what I meant to say.
BTW: do not believe everything that's in Wikipedia...

Did you test:


Sub M_snb()
CreateObject("WScript.Shell").Run "http://www.cpearson.com/Zips/FindAll.zip"
End Sub

This is also a simple alternative:


Private Declare Function F_Download 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

Sub M_snb()
F_Download 0, "http://www.cpearson.com/Zips/FindAll.zip", "G:\OF\nieuw.zip", 0, 0
End Sub

[mancubus]

@Mancubus:

That's exactly what I meant to say.
BTW: do not believe everything that's in Wikipedia...


i dont.
i had also added nonsense info to fool a friend in the past. :beerchug:

"younger i was" and not proud of that. :jsmile:

[Jannis95]

THANKS! Now it works! :)

[mancubus]

you are welcome.
i hope you have an idea why your previous attempts did not work.

please mark the thread solved from thread tools for future references.

[Jannis95]

One last Question. Sorry :D

Is there a way to click "okay" or "enter" on a popup window in Internet Explorer?

[mancubus]

as i said before, giybf!

i did not need it before. so you should work on below:

this, from
http://www.mrexcel.com/forum/excel-questions/426740-using-visual-basic-applications-close-internet-explorer-message-box-popup.html
seems to work. find clearRelItems equivalent in your page:
IE.Document.getElementById("clearRelItems").removeAttribute ("onClick")
IE.Document.getElementById("clearRelItems").setAttribute "onClick", "return true"

more:
http://www.vbaexpress.com/forum/showthread.php?38002-Excel-VBA-controlling-IE-confirmation-popup-box
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1146

keep searching...

[Jannis95]

I "googled" it the whole day.
It is a Java Script Altert.
I tried so many things eg. Sendkeys.
But it still dont work :/

[snb]

Your information is too scarce.

[Jannis95]

Your information is too scarce.

It is a message from Website Popup/Alert. I want to automaticly Close that with an VBA Code. The popup is in Java scipt.