MeltManBob
New Member
- Joined
- Apr 3, 2013
- Messages
- 7
Using vba code of a web query macro, modified to accept string variable and got rid of the .commandtype issue. A fresh run through code gives me a message box error of the query had no data but if I go in as directed to edit the definition, let it load up the IE window, the table I have coded for selection is not selected but I re-select it manually, import, refresh and from then on my code works fine looping through different strings for the query. If I let it sit for too long I have to manually edit it again then it's fine again, roughly 1-5 minutes. I've tried changing from selecting a specific table to selecting the whole page, doesn't help. I have a query with a specific table selection and another with a whole page selection, same issue on both.
Also I'm running into a text catchpa which I'm leaning towards connections within a time frame being the issue but my evidence isn't conclusive. If I run code I get 2-3 rounds from my 2 queries before I hit the catchpa. I've tried up to a 40 second hard pause after each query as well as 0-10 second random addition to the 40 and a 25 second hard pause with 0-20 second added random. Then if I manually step through the code I can typically get a few more rounds and don't need to pause as long, just enough to switch between sheets to visually see the data is doing what it should.
So if I move too fast I get a catchpa, if I wait to long I have to manually reset the query. I have a bit over 1100 rows that need data, the first query runs once per row of data but the second one typically will run up to 4 times possibly more based on how many search results are retrieved from the first query. Manually copy pasting this data is roughly 3min so that's 55 manual hours. Suggestions are welcome to this or complete alternatives, thanks in advance.
First query site example: OR secretary of state business registry lookup search result - not sure if I can post links
Second query would be an individual business registry link from the search results
The default search page with search box doesn't work for a query because of javascript but you can create a web address with your search terms and the operators to specify the type of search and for some reason the search results page and individual registry pages don't kick out errors.
Also I'm running into a text catchpa which I'm leaning towards connections within a time frame being the issue but my evidence isn't conclusive. If I run code I get 2-3 rounds from my 2 queries before I hit the catchpa. I've tried up to a 40 second hard pause after each query as well as 0-10 second random addition to the 40 and a 25 second hard pause with 0-20 second added random. Then if I manually step through the code I can typically get a few more rounds and don't need to pause as long, just enough to switch between sheets to visually see the data is doing what it should.
So if I move too fast I get a catchpa, if I wait to long I have to manually reset the query. I have a bit over 1100 rows that need data, the first query runs once per row of data but the second one typically will run up to 4 times possibly more based on how many search results are retrieved from the first query. Manually copy pasting this data is roughly 3min so that's 55 manual hours. Suggestions are welcome to this or complete alternatives, thanks in advance.
First query site example: OR secretary of state business registry lookup search result - not sure if I can post links
Second query would be an individual business registry link from the search results
The default search page with search box doesn't work for a query because of javascript but you can create a web address with your search terms and the operators to specify the type of search and for some reason the search results page and individual registry pages don't kick out errors.
