Unable extract data from WEB query

V

Vijay Kotian

I want to extract data from web. I have written a codes which is opening
CORRECTLY web site and passing login ID and Password. The page is moved to
anoher page where i pass required date (from date and to date) on submit IE
is providing required data correctly.

How these data can be extracted (downloaded / imported) into excel... When I
run the macro it imports data pertaining to Login page instead of actual data
which is appearing on IE.

Please help me out.

My codes are as under;

You may not able to access the site mentioned therein due to private domain.

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 = "GANESSR" 'Change to YOUR Login ID
.Pass.Value = "GANESSR" 'Change to YOUR password
.submit.Click
End With
' Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
' DoEvents: Loop
' Call ShowWindow(.Hwnd, 3) 'Maximize
.Visible = True
Date = Format$(Now(), "MM/DD/YYYY")
' Date = Date - 4
.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
 
V

Vijay Kotian

Hi Tim,

Thank you for your reply.

I have tried executing each command sequentially still final out put is same
( login page). What is surprising to me is with the code given below it
opens up new IE and with my last query (With
ActiveSheet.QueryTables.Add(Connection:= _
it starts quering from login page instead of report available in IE.

Can you please change codes if required to get required output.

Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top