Solved

Update worksheet to show progression of

Posted on 2013-06-07
20
146 Views
Last Modified: 2014-04-18
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
Comment
Question by:futr_vision
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39229221
No workbook has been attached.
gowflow
0
 

Author Comment

by:futr_vision
ID: 39229438
Whooops. Here it is. You could have gotten it over in the last question too.
URL-Builder-6.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39229593
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
 

Author Comment

by:futr_vision
ID: 39229700
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39229840
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
 

Author Comment

by:futr_vision
ID: 39230331
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
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 500 total points
ID: 39230698
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39230719
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39231200
I am using Excel 2003 and 2007. I have win 7 64bits and do not have 2010 and this is the issue.
gowflow
0
 

Author Comment

by:futr_vision
ID: 39233667
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 500 total points
ID: 39233769
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
 

Author Comment

by:futr_vision
ID: 39234331
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
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39236569
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
 

Author Comment

by:futr_vision
ID: 39236606
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
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 39250871
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
 

Author Comment

by:futr_vision
ID: 39251460
I've been super bust this week too. I'll give this a try.
0
 

Author Comment

by:futr_vision
ID: 39292078
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
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39308349
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
 

Author Comment

by:futr_vision
ID: 39408871
Not going to need this now unfortunately and I can't really provide any more information than I already have.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39409071
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now