Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable extract data from WEB query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable extract data from WEB query
You need to add a wait after submitting the login form.
Tim "Vijay Kotian" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable extract data from WEB query
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:= _ "URL;http://10.6.4.48/institution/TransactionReport.jsp", Destination:=Range( _ "A1")) 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. "Tim Williams" wrote: You need to add a wait after submitting the login form. Tim "Vijay Kotian" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web query..... unable to get data ? | Excel Discussion (Misc queries) | |||
Extract Data using Web query | Excel Programming | |||
Data extract from access query | Excel Discussion (Misc queries) | |||
Unable to open MSN stock quote query in get external data in Exce | Excel Programming | |||
Using Microsoft Query to Extract Data | Excel Programming |