• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

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.
0
futr_vision
Asked:
futr_vision
  • 9
  • 5
  • 4
  • +1
3 Solutions
 
gowflowCommented:
No workbook has been attached.
gowflow
0
 
futr_visionAuthor Commented:
Whooops. Here it is. You could have gotten it over in the last question too.
URL-Builder-6.xlsm
0
 
gowflowCommented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
futr_visionAuthor Commented:
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.
0
 
gowflowCommented:
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
0
 
futr_visionAuthor Commented:
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.
0
 
ltlbearand3Commented:
futr_vision,

I must be a gluten for punishment.  After this:
        ' We moved the data.  We need to find the bottom range again
        Set rngDataCurrent = objDataSheet.Cells(objDataSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)

Open in new window

Add
        rngURLCurrent.Offset(0, 1).Value = "X"

Open in new window


-Bear
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi goflow,

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:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28144042.html#a39214497 ]

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28144042.html#a39214926 ]

BFN,

fp.
0
 
gowflowCommented:
I am using Excel 2003 and 2007. I have win 7 64bits and do not have 2010 and this is the issue.
gowflow
0
 
futr_visionAuthor Commented:
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.
0
 
ltlbearand3Commented:
Try this attached code.  It should clear the Xs when either you update the URL Date or Interval or you run the Web Query Code.

As far as marking this only when successful.  See if this happens.  We were also deleting rows even if no data was added so I tried to adjust for that.
URL-Builder-7.xlsm
0
 
futr_visionAuthor Commented:
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.
0
 
ltlbearand3Commented:
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
0
 
futr_visionAuthor Commented:
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.
0
 
ltlbearand3Commented:
futr_vision,

Sorry I have been busy this week and setting up something to try and duplicate your scenario has proven difficult.  Right now I cannot figure out what is happening on you systems as I can not duplicate the results.  Right now what I would like you to do is take this version and post the Immediate Window results of the debug.print.  Make sure to grab this as soon as the VPN disconnects.  

-Bear
URL-Builder-8.xlsm
0
 
futr_visionAuthor Commented:
I've been super bust this week too. I'll give this a try.
0
 
futr_visionAuthor Commented:
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
0
 
ltlbearand3Commented:
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
0
 
futr_visionAuthor Commented:
Not going to need this now unfortunately and I can't really provide any more information than I already have.
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 9
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now