Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Web Query does not seem to get the data
Is anybody able to assist here? I recorded a macro to get the code i needed
over getting data from a web page.. basically i wn=ant to grab all provider information and have the sheet update itself every so often with additions and deletions. Anyway, when i did the web query and had the entire region of data i needed selected, i hit the button to import it, and the text never came across except two titles of "location" and "name" Is there a reason the mass of data didn't import? I had no problem with another site i ge data off. The macro that grabs data (part only) reads as follows: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.workcover.nsw.gov.au/workcover/Templates/SearchRehabProvider.aspx?NRMODE=Published&NRORIGIN ALURL=%2fWorkcoverOnline%2fRehabProviders%2fdefaul t%2ehtm&NRNODEGUID=%7bAB690DDF-A1F0-4C55-8DB5-7EE7417B756C%7d&NRCACHEHINT=Guest" _ , Destination:=Range("A1")) .Name = _ "SearchRehabProvider.aspx?NRMODE=Published&NRORIGI NALURL=%2fWorkcoverOnline%2fRehabProviders%2fdefau lt%2ehtm&NRNODEGUID=%7bAB690DDF-A1F0-4C55-8DB5-7EE7417B756C%7d&NRCACHEHINT=Guest" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "15" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.SmallScroll Down:=-6 Range("D5").Select the website is www.workcover.nsw.gov.au then i go to the "WorkCover Online" option on the left side of screen, then on right side i select on find a rehabilitation provider amd select all of them so all display Basically I am trying to have the macro grab all results... preferrably going into each result and grabbing the provider number for each one as well. Anybody able to assist in why the macro is not grabbing the data as it should be? Thank you so much in advance if you can help. Clint |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query does not seem to get the data
Clinton,
This method of sending all the parameters in the URL is not guaranteed to work. It depends on how/what the server is expecting. As the page is ASPX with some hidden (and encrypted ?) fields, I suspect you may need to delve more deeply into the HTML/DOM etc. or resort to another method, maybe Automation IE. NickHK "Clinton M James" wrote in message ... Is anybody able to assist here? I recorded a macro to get the code i needed over getting data from a web page.. basically i wn=ant to grab all provider information and have the sheet update itself every so often with additions and deletions. Anyway, when i did the web query and had the entire region of data i needed selected, i hit the button to import it, and the text never came across except two titles of "location" and "name" Is there a reason the mass of data didn't import? I had no problem with another site i ge data off. The macro that grabs data (part only) reads as follows: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.workcover.nsw.gov.au/work...hRehabProvider ..aspx?NRMODE=Published&NRORIGINALURL=%2fWorkcover Online%2fRehabProviders%2fd efault%2ehtm&NRNODEGUID=%7bAB690DDF-A1F0-4C55-8DB5-7EE7417B756C%7d&NRCACHEHI NT=Guest" _ , Destination:=Range("A1")) .Name = _ "SearchRehabProvider.aspx?NRMODE=Published&NRORIGI NALURL=%2fWorkcoverOnline% 2fRehabProviders%2fdefault%2ehtm&NRNODEGUID=%7bAB6 90DDF-A1F0-4C55-8DB5-7EE74 17B756C%7d&NRCACHEHINT=Guest" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "15" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With ActiveWindow.SmallScroll Down:=-6 Range("D5").Select the website is www.workcover.nsw.gov.au then i go to the "WorkCover Online" option on the left side of screen, then on right side i select on find a rehabilitation provider amd select all of them so all display Basically I am trying to have the macro grab all results... preferrably going into each result and grabbing the provider number for each one as well. Anybody able to assist in why the macro is not grabbing the data as it should be? Thank you so much in advance if you can help. Clint |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
How to Process Query Data after Query is Refreshed | Excel Programming | |||
Query returning more data than will fit on worksheet with VBA DB query... | Excel Programming |