Web query problem when password required
 

Hi, a i have code (see bellow) with web query but the page required login.
the page is http://www.hockeyarena.net/hokej.php and all i need is to connect
and import one table but login doesn't work. I can't figure out why? Maybe it
is because of wrong form number but i have tryid other numbers and it didn't
help. Any sugestion how solve this problem will be appreciated. Thanks

Henrich

- - -

Public Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)
Sub Trening()
Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Navigate "http://www.hockeyarena.net/hokej.php"
Do While .Busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
With .Document.forms(8)
.UserName.Value = "xxx"
.Password.Value = "xxx"
.login.Click
End With
Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
DoEvents: Loop
Call ShowWindow(.hwnd, 3) 'Maximize
.Visible = True
End With
Set ie = Nothing

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hockeyarena.net/manager_training_form1.php",
Destination:= _
Range("A1"))
.Name = "manager_training_form1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub

Web query problem when password required
 

Hi, you will have to iterate through a series of IHTMLInputElements of type
TEXT and given-name, locate the appropriate ones, set the data, iterate
through a series of IHTMLInputElements of type button/submit and given-name
and invoke the click event. The forms number may not match sometimes.

"Henrich" wrote:

Hi, a i have code (see bellow) with web query but the page required login.
the page is http://www.hockeyarena.net/hokej.php and all i need is to connect
and import one table but login doesn't work. I can't figure out why? Maybe it
is because of wrong form number but i have tryid other numbers and it didn't
help. Any sugestion how solve this problem will be appreciated. Thanks

Henrich

- - -

Public Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)
Sub Trening()
Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Navigate "http://www.hockeyarena.net/hokej.php"
Do While .Busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
With .Document.forms(8)
.UserName.Value = "xxx"
.Password.Value = "xxx"
.login.Click
End With
Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
DoEvents: Loop
Call ShowWindow(.hwnd, 3) 'Maximize
.Visible = True
End With
Set ie = Nothing

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hockeyarena.net/manager_training_form1.php",
Destination:= _
Range("A1"))
.Name = "manager_training_form1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub

Web query problem when password required
 

Madhan, thanks for your reply first.

The problem with login is solved but the problem with query is not yet
solved. My code just open IE, log in and open the reguested page but when it
should query the page the resuld is that in excel is as result that i don't
have permission for the requested page. So i think that the excel doesn't
hold the nick and password. Have you any idea how could be this done? Thanks

Henrich

"Madhan" wrote:

Hi, you will have to iterate through a series of IHTMLInputElements of type
TEXT and given-name, locate the appropriate ones, set the data, iterate
through a series of IHTMLInputElements of type button/submit and given-name
and invoke the click event. The forms number may not match sometimes.

"Henrich" wrote:

Hi, a i have code (see bellow) with web query but the page required login.
the page is http://www.hockeyarena.net/hokej.php and all i need is to connect
and import one table but login doesn't work. I can't figure out why? Maybe it
is because of wrong form number but i have tryid other numbers and it didn't
help. Any sugestion how solve this problem will be appreciated. Thanks

Henrich

- - -

Public Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)
Sub Trening()
Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Navigate "http://www.hockeyarena.net/hokej.php"
Do While .Busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
With .Document.forms(8)
.UserName.Value = "xxx"
.Password.Value = "xxx"
.login.Click
End With
Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
DoEvents: Loop
Call ShowWindow(.hwnd, 3) 'Maximize
.Visible = True
End With
Set ie = Nothing

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hockeyarena.net/manager_training_form1.php",
Destination:= _
Range("A1"))
.Name = "manager_training_form1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub