How to make a simple status report with only Excel in hands...

O

off-patience

Registered Users (C)
Guys, i got a lot of requests to make a status snapshot report for such and such day but since the process is very long (especially initial run)

i just cannot do that for all. I prepared a little VB script that can automate status request for you:

1. Run Excel
2. Tools|Macro|Visual Basic Editor
3. Under "VBA Project" item click on "ThisWorkbook" item
4. Paste the attached VBA script (it is very simple, although, verify that it does not contain any harmful activities, since I cannot guarantee that the attachment is NOT subsitiuted or changed on this board)
5. Save your workbook
6. Close VB EDitor
7. Tools|Macro|Macros...
8. Run "StartScan" function
9. Enter the requested parameters and wait till "done" message
10. Tools|Macro|Macros...
11. Run "MakeChart" function

Note 1: For your security!
If you want to hide your IP address and run the browser anonymously use one of the public anonymous proxy servers: (e.g. from here: http://atomintersoft.com/products/alive-proxy/proxy-list/?ap=1#proxy-list or from any other sources you may know)

To change proxy server setting of your Browser:
(BEFORE you run the Excel script)
1. Launch InternetExplorer and go Tools|Options|Connections Tab|Lan Settings...
2. Check "Use Proxy"
3. Enter proxy server IP and port
4. When you done with Excel just uncheck proxy server settings.

Note 2:
When you run StartScan for the first time, all WACs in the given range will be checked. When you run it next time, the existing WAC list will be used. Approved and denied statuses will not be verified and updated.

Note3: You can run multiple Excels and assign different ranges for each process and then combine results into one sheet. This may improve performance but will definetely stress the server. So, i would not recommend doing this.

Note4: Feel free to customize the script, for instance to support other application types.

Please use the latest version attached lower in this thread.
 
Last edited by a moderator:
Good job, off-patience

You have done a wonderful job :) of making a I-485 filter!
Thanks for sharing.
 
Last edited by a moderator:
WAC-02-103 AOS Summary

Following is the WAC-02-103 AOS summary .

Approved/Completed Count 5
Received Count 36
Resumed/FP recvd Count 136
Grand Count 177

Excellent service by off-patience.
 
request / concern

Guys,
Off-patience has done a good job and with good intention he has attached the script.

Please do not bang on the server as it MIGHT end up badly. This is just a concern and this should not end up closing the INS option of checking status online.

I know its difficult to co-ordinate and run this. Just keep in mind the problems in banging the server.

If I can, I would request off-patience to remove the attachment fro this thread, so that it wont be very widely distributed.

Hope you guys will understand. Thanks.
 
I disagree with Atri

I think what off-patience wrote is a great utility and should be used by people here.

As for INS their site is a portal and it appears to be sufficiently powered to handle the "initial" pounding as you say. Once the intial scan is done, the next pass with the data is relatively light.

Not knowing but a reasonable guess as to why INS has this site is to off load the "pounding" on their phone system and ultimately calls to the IIO's. I would imagine like most places they are more than happy to provide this customer self service and since it is only inquiry lookups, the load on their backend database is light.

Having set up customer self service portals, our view of success is utilization and you would be surprised how few utilization statistics and monitoring software is actually used on these sites however where they are used they are seen as a justification of the need for the site if there is more usage.

So I say, definetly not, do not remove this thread or script by off-patience. It's a fantastic tool which finally lets us get an acurate view on what INS is doing and what progress is happening.

You look at all the web sites, the i485 tracking site, rupnet etc which are a best attempt at trying to determine trends and make speculations. Here now we can know definitively the progress.

Great job off-patience!
 
Atri123, i understand your concern...

What we can do here to lower the server usage is to track each day only by a single person.
For instance, i can continue to track 02-041, wonderfull - 02-103, and so on.

So, Guys, let's distribute the WAC days here, so others will not duplicate your work of creating filtered I-485 WAC lists and will not stress the server without need.

BTW, when i run the tool to update the status for existing list i found a few problems. So, i am attaching the fixed version here.
Please use it instead of the original one.
 
I agree to off-patience's proposal. I will track WAC-02-103 regularly when I am not travelling. Should not be an issue
as 103 approvals are few months away.

Off-patience:

Re-using the initial run data was a great idea.
You are a thorough designer and coder.:D
 
Thanks off-patience. (for your work and understanding) :)

This idea seems better.

Hope you guys understand that I am not against this. I also need this info and thats why visiting this forum too often nowadays.

Earlier, there was another thread in which our folks were discussing about getting the stats from the INS site and they finally decided to remove that thread as there might be some traffic issues on the site.

Thats why I was concerned. I am not a coder and I have no idea how this code OR a java code is going to affect the site. I just raised my concern as I CERTAINLY DO NOT WANT ANYONE TO JEOPARADISE THE ONLINE CASE STATUS CHECK.
 
Please use the code deligently. The last thing we want is to have tech problems on this server. If a few guys post results, we can all follow. There are other trackers written to generate pie charts and other statistical information in a couple of other threads. Please check them out on this board before attempting to generate data.

good luck all...
 
It is a wonderful tool. But anytime I select the default wac nubmer:
50000-59999, it pops out a waring "Incorrect Value".

Do you know how to fix that???
 
use this script

Hi,

Even i encountered the same pblm. I just added a line in the vb script where it clears the Err object.

Try this script...it should work fine
 
Originally posted by digdugxy
It is a wonderful tool. But anytime I select the default wac nubmer:
50000-59999, it pops out a waring "Incorrect Value".

Do you know how to fix that???
Click to expand...

OOPS, sorry guys i did not test the 2nd version of the script for new scans... (since i'm running only updates now ;) )

pill proposed a right solution, just insert the line

Err.Clear

before the one that contains second (!) InputBox
 
Script is really cool!!

But does it show true status from INS web site since I know web site status is not true as far as my case goes, I have already received my plastic card and still it shows that we have received your application on Sep 04 2002 and it is taking ....., where as MY ND was April'01. But some thing is better than nothing.
 
please use new version

PCEE reported some problems related to VBA scripting engine. Hope this version can fix it. (let me know if you have any problem with it)

Also, I tried to standardize status pie chart.
MakeChart macro will generate a chart with a standard size and predefined color for each status group.
It will also automatically copy and paste the image into the Paint application. You will just need to save it as a GIF file and post it to the forum. If your Paint does not support save as GIF, then you probably need to install corresponding graphics filter from MS Office package or use other paint/photo software that can do it.

I am going to open a thread that is supposed to have only chart images, each message must be the different WAC day from those guys who support their WAC lists, so we can all easily compare the different ND charts as well as the progress.

Once you have a chart update, just post a new message with a new chart image and brief summary of changes.
 
Some modification

Thank you for your script

I modified it a little. I've never been a guru in VBasic. So don't expect high coding standards.

Whats new

1. History column added to see what previous state of application was
2. Approved/Completed/Card ordered are splitted and checked when list updated. If you are not interested in them and want to save 5 minutes either remove them from list or update script.
3. Added some new statuses.

4. First run create full list of all application in that day, not only I485. This need some explanations

Sometimes queries show "record not found" message. But next run found them. Probably it because database was updated/ maintaned in first time.
So my sequence of running scriptis
- Create full list for all application
- Copy it into different worksheet with different name
- Run "Make chart script" for sorting and analyse list.
- Remove type of application you are not interested in.
- Remove "Not found" lines at the end of list but left such lines inside application pull. Probably they will be restored on next run.
- Later run Scan script in Update mode as usually.

Later it can be modified to generate update report in Silly Man style. I think we have a lot of time before approval to do that. :-))

Viktor
:)
 
a thank you note for off-patience!

i first discovered your script which was posted by someone in a thread, which i modified to run for LIN cases, and since then we have been using it a lot in the NSC forums.

i wanted to find out who the original author of the script was so i could thank him/her, but i found no indication of that from the script itself. the excel file properties indicated the author of it was "greenland" (probably the nick of the guy who had saved that particular file), so for want of a name, i decided to call the script the "Greenland Script", and the collection of reports generated from it the The Greenland Report, which name seems to have caught on in the NSC forums.

a user in the VSC forum pointed me to this thread, so finally, i have the opportunity to say to you, off-patience: Thank you!.
 
Last edited by a moderator:
You must log in or register to reply here.
Top