futr_vision
asked on
Update worksheet to show progression of
Here is a link to the previous question that this will be applied too. Previous question
I am also attaching the final workbook used in that question since that is what this question is based on.
The macro runs through the list of URL's on the "URLs" worksheet and pulls data from the web and writes it to the "Data" worksheet. What I would like to see happen is an "x" be placed in the column next to each query after the data is successfully written to the "Data" worksheet.
I am also attaching the final workbook used in that question since that is what this question is based on.
The macro runs through the list of URL's on the "URLs" worksheet and pulls data from the web and writes it to the "Data" worksheet. What I would like to see happen is an "x" be placed in the column next to each query after the data is successfully written to the "Data" worksheet.
ASKER
Whooops. Here it is. You could have gotten it over in the last question too.
URL-Builder-6.xlsm
URL-Builder-6.xlsm
I did actually as I got an error I tried your attached file and gives same issue and here it is:
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
is highlighted in Red meaning it is not a valid instruction is it working your end ???
Also get an error at
Sleep 50
Sub or Function Not defined I guess it is a resultant from the previous Declared function.
gowflow
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
is highlighted in Red meaning it is not a valid instruction is it working your end ???
Also get an error at
Sleep 50
Sub or Function Not defined I guess it is a resultant from the previous Declared function.
gowflow
ASKER
The URL's are dummy URLs and so is the username and password. I'm running Excel 210 64-bit so that is likely why you get an error for the Declare statement. The error at Sleep 50 is probably URL related.
mmm ic
so basically what you want is that when the url is executed to have an x in the column that is just after the url ? is that what you want ?? as cannot test it !!!
gowflow
so basically what you want is that when the url is executed to have an x in the column that is just after the url ? is that what you want ?? as cannot test it !!!
gowflow
ASKER
That is pretty much what I am going for but the "x" should only be placed if the query completes and data is returned. If there is no way to do that the I will settle for an "x" when the query is executed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi goflow,
The reason for this, I am guessing, is that you are using a 32-bit version of Microsoft Excel. futr_vision is using the 64-bit version of Excel 2010.
Please see my two comments within the related question, here:
[ https://www.experts-exchange.com/questions/28144042/Exccel-2010-How-to-run-multiple-web-queries-in-succession-and-record-data-in-single-table.html?anchorAnswerId=39214497#a39214497 ]
[ https://www.experts-exchange.com/questions/28144042/Exccel-2010-How-to-run-multiple-web-queries-in-succession-and-record-data-in-single-table.html?anchorAnswerId=39214926#a39214926 ]
BFN,
fp.
I did actually as I got an error I tried your attached file and gives same issue and here it is:
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
is highlighted in Red meaning it is not a valid instruction is it working your end ???
Also get an error at
Sleep 50
Sub or Function Not defined I guess it is a resultant from the previous Declared function.
The reason for this, I am guessing, is that you are using a 32-bit version of Microsoft Excel. futr_vision is using the 64-bit version of Excel 2010.
Please see my two comments within the related question, here:
[ https://www.experts-exchange.com/questions/28144042/Exccel-2010-How-to-run-multiple-web-queries-in-succession-and-record-data-in-single-table.html?anchorAnswerId=39214497#a39214497 ]
[ https://www.experts-exchange.com/questions/28144042/Exccel-2010-How-to-run-multiple-web-queries-in-succession-and-record-data-in-single-table.html?anchorAnswerId=39214926#a39214926 ]
BFN,
fp.
I am using Excel 2003 and 2007. I have win 7 64bits and do not have 2010 and this is the issue.
gowflow
gowflow
ASKER
Bear,
That works pretty good but it has a little flaw. If a query fails it still gets marked with an "X". I'd like it to be marked with an "X" only if the data was downloaded and written to the "Data" worksheet. Is that possible? If those "X"'s can also be cleared from that column if the routine is restarted that would be great too. The ultimate goal is to figure out which was the last query that successfully ran before an error a manual kill of the routine so I can up from that point with my next attempt.
That works pretty good but it has a little flaw. If a query fails it still gets marked with an "X". I'd like it to be marked with an "X" only if the data was downloaded and written to the "Data" worksheet. Is that possible? If those "X"'s can also be cleared from that column if the routine is restarted that would be great too. The ultimate goal is to figure out which was the last query that successfully ran before an error a manual kill of the routine so I can up from that point with my next attempt.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Bear. This works pretty good. "X"s get deleted when the subroutine is restarted just like the data on the "Data" worksheet. They do not get deleted when I change the date or interval but I don't think that is a big deal because as soon as I run the subroutine they are in fact deleted. There is one issue however. Each query gets marked with an "X" even when the report fails to run due to a lost connection to the VPN. so basically what happens is the it tries to run the query and get's returned an IP address. The subroutine recognizes this as a completed query and moves on to the next. Any way to prevent that? Maybe end the subroutine when and IP address is returned.
What is odd now is that one of the older subroutines we ran used to record the IP address on the "Data" worksheet. It no longer does that. I'm not complaining about that but I thought I'd mention it.
What is odd now is that one of the older subroutines we ran used to record the IP address on the "Data" worksheet. It no longer does that. I'm not complaining about that but I thought I'd mention it.
futr_vision,
I am at a little bit at a loss at the moment since I cannot see the actual results. Remember we have change several items at different stages including deleting rows. That may be why it shows an IP address. Please post two samples for me. One from the current version showing me where on the DATA page is the "missing" data and then show the corresponding X. Also post the sample DATA page fromthe earlier version where it returned an IP address. Please let me know what version of code you ran for that result. Thanks.
-Bear
I am at a little bit at a loss at the moment since I cannot see the actual results. Remember we have change several items at different stages including deleting rows. That may be why it shows an IP address. Please post two samples for me. One from the current version showing me where on the DATA page is the "missing" data and then show the corresponding X. Also post the sample DATA page fromthe earlier version where it returned an IP address. Please let me know what version of code you ran for that result. Thanks.
-Bear
ASKER
Bear,
We can ignore my last statement. It is not really that important in the grand scheme of things. My biggest issue is the "X" showing up next to URL queries that are submitted but do not return any data. This only happens when I disconnect or I am disconnected from the VPN. At that point I cannot access the reporting center. Instead an IP address is returned. Unfortunately, the subroutine see this as a successful query and marks an "X" next to that query and moves onto the next which yields the same result. It only ends when it gets to the end of the URL query list or I kill the process.
We can ignore my last statement. It is not really that important in the grand scheme of things. My biggest issue is the "X" showing up next to URL queries that are submitted but do not return any data. This only happens when I disconnect or I am disconnected from the VPN. At that point I cannot access the reporting center. Instead an IP address is returned. Unfortunately, the subroutine see this as a successful query and marks an "X" next to that query and moves onto the next which yields the same result. It only ends when it gets to the end of the URL query list or I kill the process.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've been super bust this week too. I'll give this a try.
ASKER
Took me awhile to get back to this but this is what ends up in the output window regardless of whether i am connected to the VPN or not.
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
Is Cell an IP Address? = False
futr_vision,
Sorry for the delay. I am going to need some additional information to figure out how to determine if we had a good result or not. Is there something you can see that will help us determine if the query was correct or not? We need to be able to look at the returned data and determine if the result is good or not. Based on your earlier comments, I wanted to look for an IP address but that does not seem to be working. Since I cannot see the real data, I am not sure what to do to determine if the result was good or not.
-Bear
Sorry for the delay. I am going to need some additional information to figure out how to determine if we had a good result or not. Is there something you can see that will help us determine if the query was correct or not? We need to be able to look at the returned data and determine if the result is good or not. Based on your earlier comments, I wanted to look for an IP address but that does not seem to be working. Since I cannot see the real data, I am not sure what to do to determine if the result was good or not.
-Bear
ASKER
Not going to need this now unfortunately and I can't really provide any more information than I already have.
I was just looking at this thread yesterday wondering if you were ever going to return.
Such a shame to waste Bear's efforts but, given that a solution was not found, the typical outcome is that the entire thread is deleted.
Such a shame to waste Bear's efforts but, given that a solution was not found, the typical outcome is that the entire thread is deleted.
gowflow