Macros for WEB Query with password protected site

[Vijay Kotian]

HI,

I would like to extract data from site which has password protection.
(http://10.6.4.48/institution/Login.jsp) where user name and password needs
to be given. After passing login screen the screen is move to
(http://10.6.4.48/institution/welcome.jsp). The particular address has few
dropdown menu's and on selection of dropdown menu what I required the
address changes to (http://10.6.4.48/institution/TransactionReport.jsp).

On providing dates (from and to) and by pressing OK button the data is
displayed. I want these data but unable to write macro for the same.

The macro recorded does not include all above steps or it directly goes to
(http://10.6.4.48/institution/TransactionReport.jsp) and unable to get any
data.

Can anyone help me out to write macro for these.

Thank you

 

[Vijay Kotian]

HI

The URL mentioned by me is our internal office software URL. I would like to
extract data and import to excel. I have tried to write macro which is as
under;


Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://10.6.4.48/institution/Login.jsp"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms(0)
.UID.Value = "GANESHS" 'Change to YOUR Login ID
.Pass.Value = "GANESHS" 'Change to YOUR password
.submit.Click
End With
.Visible = True
Date = Format$(Now(), "MM/DD/YYYY")
.navigate "http://10.6.4.48/institution/TransactionReport.jsp"
With .Document.Forms(0)
.txtFromDt.Value = Date
.txtToDt.Value = Date
.submit.Click
End With
End With

Exit Sub


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://10.6.4.48/institution/TransactionReport.jsp",
Destination:=Range( _
"A1"))
' .Name = "TransactionReport"
.Name = "AllEntries"

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
' .WebTables = x
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing


The code written by me opens a separate internet explorer and data is shown
at IE. The same cannot be imported to excel. The data which is downloaded
(with above code) shows the first page i.e. login and password.

How is it possible to get data which is appearing on IE with above code. OR
any other alternative. Please...

Thank you.